Howdy, Stranger!

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

Categories

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

  • dokken2dokken2 Member Posts: 532
    : 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 & "') "
  • WEBMASTERWEBMASTER Member Posts: 549
    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.