getting records from access 97 database using date in sql statement

[tyedyejenn]

My program is written in VB6 and accesses an Access 97 database. In my table I have a field called DateEntered that has a data type of date/time. In my program I have a variable called strActivityDate. I have tried to declare strActivityDate as a variant, a string and a date and I keep getting
"Data Type Mismatch in criteria expression"

here is my sql statement
"Select * from TrafficSheetsSentToAgents Where TrafficSheetsSentToAgents.AgentNumber =" & "'" & Trim(strLookUpAgent) & "'" & " and TrafficSheetsSentToAgents.DateEntered = " & "'" & strActivityDate & "'" & ""

If I change the datatype in the table to text I don't get the error however then I have to contend with different formats of date from 150 users. Any help is GREATLY appreciated and needed. Thank you in advance

Jenn

 

[Ruairi]

The datequote in access should be # not '. change your sql statement to surround the date variable with 2 #'s instead of of single quotes. You should be able to use a date datatype for the variable.

Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[tyedyejenn]

Ruairi

Thank you very much. It works!! This has caused me agreat deal of frustration. I appreciate your quickness in replying. Have a great day

Jenn