Problem with Date/Time as criteria in SQL Statement

[Ads]

Hey...
I am building a basic transaction program to be used at a Tanning Salon. When a staff member logs off, I want to query the recordset to focus to records for that Staff Member, on that Date, between login/logout times; this will enable calculation of Shift Sales, Packages, Totals...

Here is a rough template of the function that is called when the staff member logs off. I have tested it with acurate records, but to no aval.


*************************

Public Sub CalculateCashOut()

Dim strSQLCashOut As String
Dim intDate As Date

strSQLCashOut = "SELECT TransactionLotion.*, TransactionPackage.*, TransactionLotion.StaffIDFK, TransactionPackage.StaffIDFK, TransactionLotion.OrderDate, TransactionPackage.OrderDate " & _
"From TransactionLotion, TransactionPackage " & _
"WHERE TransactionLotion.StaffIDFK=" & mStaffID & " AND TransactionPackage.StaffIDFK=" & mStaffID & " AND TransactionLotion.OrderDate=" & Date & " AND TransactionPackage.OrderDate=" & Date & " ;"

datCustomerPackage.RecordSource = strSQLCashOut
datCustomerPackage.Refresh

Do While datCustomerPackage.Recordset.EOF = False

If datCustomerPackage.Recordset("TransactionPackage.OrderTime") >= frmLogin.txtFields(2).Text And datCustomerPackage.Recordset("TransactionPackage.OrderTime") <= frmLogin.txtFields(4).Text Then
If datCustomerPackage.Recordset("TransactionLotion.OrderTime").Value >= frmLogin.txtFields(2).Text And datCustomerPackage.Recordset("TransactionLotion.OrderTime") <= frmLogin.txtFields(4).Text Then

MsgBox "You Got Into the Loop", vbOKOnly

End If
End If

Loop


End Sub

*********************

If anyone has any comments, I would be greatly thankful.
Ads

 

[vb5prgrmr]

Ads have you read FAQ222-2244 item 15 yet?

Ok, grab a blank access database and create a dummy table with a date field in it and start to add records to it. Then use the query designer to create simple recordsets based upon the date criteria you want. Switch to SQL view and take a look at the syntax.

Now take what you have learned from that and start with one of the tables that has your date in it. Experiement a little as needed. Then build up the correct syntax from there to get what you want from the production database.

Why, go this route? Well for learning the basics of SQL I can think of no better tool than the Access Quiry Builder (in Design View).

Now if you have not figured out yet what you need to do to correct it I will give you a hint. Look at how and what is around the date field in your dummy database then look at your SQL statement you have above. See any difference?

Good Luck

 

[DrJavaJoe]

or do a keyword search here, this question has been done to death.

&quot;Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.&quot;

 

[vb5prgrmr]

Ads have you read FAQ222-2244 item 15 yet? Have you solved your problems yet?

Good Luck

 

[Ads]

Thanks Everyone! I went live with this project yesterday, and everything is running smoothly.

Ads