storing monthyear in the same field

how to store the month and year in the same field in the database using a query....i have a query which stores current month and year in the different fields.... but i want to store in the form october2007 or 10/2007


Private Sub cmdAdd_Click()



Dim m As String
Dim y As String
Dim d As Date

d = Now()
m = MonthName(Month(Now))
y = Year(Now)

With AConn
.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=E:GraphGraphxpence.mdb"
.Open
End With
sSQL = "INSERT INTO Table1(TodayDate,MonthName,YearValues,Amount,Description) " & _
" Values (# " & Format(d, "dd/mm/yyyy") & " #,' " & m & " ',' " & y & " ', ' " & Text3.Text & " ', ' " & Text2.Text & " ') "

AConn.BeginTrans
AConn.Execute sSQL
AConn.CommitTrans
AConn.Close

End Sub


this is the query ...what changes should made to obtain monthyear form......

using vb6.0 and backend is access

Comments

  • : how to store the month and year in the same field in the database
    : using a query....i have a query which stores current month and year
    : in the different fields.... but i want to store in the form
    : october2007 or 10/2007
    :
    :

    you can concatenate the month,year into a variable, then add that variable to your INSERT statement-

    Dim myMonthYear As String
    Dim myShortMoYr As String

    myMonthYear = MonthName(Month(Now)) & " " & YEAR(Now)
    MsgBox myMonthYear
    myShortMoYr = Month(Now) & "/" & YEAR(Now)
    MsgBox myShortMoYr

    ...
    'your sql statement
    ...
    ssql = "INSERT INTO Table1(TodayDate,MonthName,YearValues,Amount,Description,MONTHYEAR) " & _
    " Values (# " & Format(d, "dd/mm/yyyy") & " #,' " & m & " ',' " & y & " ', ' " & txt3 & " ', ' " & txt2 & " ', '" & myMonthYear & "') "
  • I usually converts the data into a "integer" form like


    Year*65536+month*256+Day

    By using a integer field it will be faster to do queries against it and it will consume less space in the database
    Tore Nestenius
    [blue][italic][b]/WEBMASTER[/b][/italic][/blue] Programmers Heaven
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories