hi guys,
does anyone have an idea how i could generate a monthly,daily or annual report using sql statement since i have tried with the one for daily and i keep getting the error 'now' is not a recognized function name.
i want to get the total of Amount column and VAT column separately for todays transactions and this is the code i write

pubSQl = "SELECT SUM(AMOUNT) As TotalAmount,SUM(VAT)As TotalVAT from TransactionsToday WHERE DateAdded = 'now'"


when i run it i get the error above.but when i put a specific date like 05/10/2010 it works fine.my aim is to have a daily report,monthly report and annual report command buttons such that when u click it generates the report.
any help is highly appreciated

Try and change the following -

Today WHERE DateAdded = 'now'"

to,

...Today WHERE DateAdded = DateValue(" & "'" & "Date" & "'"

hi Andre i have tried as u suggested bt i am getting the error below

[Microsoft][ODBC SQL Server Driver][SQL Server]line 1:Incorrect syntax near 'Date'

Sorry, my typo. Rather try the following -

format$(Now, "mm/dd/yyyy")
...Today WHERE DateAdded =" & "'" & "Format$(Now, "mm/dd/yyyy")" & "'"

ngayon = format(Now,"mm/dd/yyy")
....Today WHERE DateAdded = '" & ngayon & "'"

complete coding to display record in between two dates. I Tryed many times with between date=# ..# And date=# ..# . So kindly send any one example coding ... Thank You

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.