I believe you can also use the Single-Quote Character ' as well as the #.
This way the Query will be both compatible with Access and SQLServer.

Select * From [Table] Where Date <= '01/01/2001' And Date >= '12/31/2001'

-----Original Message-----
From: John Ruff [mailto:papparuff@c...]
Sent: Tuesday, May 29, 2001 4:51 PM
To: professional vb
Subject: [pro_vb] RE: Access and Dates


Patrick,

As stated in a previous thread, Access uses the # sign when setting criteria
for dates.

Your select statement should be:

Select * from MispackData where StampDate = #05/29/2001#

Or if you are using a variable in Select statement it should read;

Select * from MispackData where StampDate = #" & datDate & "#"

Instead of

Select * from MispackData where StampDate = 05/29/2001

I hope this helps.

John Ruff - The Eternal Optimist :)

 -----Original Message-----
From: 	LaFerriere, Patrick M. [mailto:PLaFerriere@T...]
Sent:	Tuesday, May 29, 2001 7:34 AM
To:	professional vb
Subject:	[pro_vb] Access and Dates

I asked this once before and I thought I had it licked but I don't. I have
two vb apps. One that does data entry to an access database and another that
creates reports and charts from it. I have a date field and it is formatted
Date/Time.......with this as the format mm/dd/yyyy. The code I am using to
input the date looks like this....

'set up the record that is to be put in the database
With tDataEntry
  .Comments = Me.txtComments & ""
  .Machine = Me.cboMachine & ""
  .PrdBase = Me.cboBase & ""
  .PrdDesc = Me.cboPrdDesc & ""
  .PrdType = Me.cboPrdType & ""
  .tDate = Format(CDate(Me.txtDate), "mm/dd/yyyy")
  .Technician = Me.cboTech & ""
  .MisPackType = Me.cboMisPackType & ""
  .Shift = Me.cboShift & ""
End With

And the query that returns the report data looks like this....

Select * from MispackData where StampDate = 05/29/2001

When I look at the database I see the date formatted correctly.....when I
query I get time back. I know this is because Access automagically adds the
time but I can't reformat the date when it is returned. I tried the
DateValue function, the Cdate function and no luck.

Thanks in advance
Patrick LaFerriere
Analyst/Programmer , MCP
Transitions Optical
xxx.xxx.xxxx  Ext. 2222
PLaFerriere@T...