newbie need help

I need to know how to write a sql statement for VB to Microsoft Access for the following criteria.

TblEmployees only Field to be concerned with is EmployeeID -Its PK
TblEmpAttendane has these fields:
EntryID - Pk
EmployeeID - Fk
Date- A text form date validated through vb to avoid hassle
Value-A single digit value that can be alpha OR numeric

To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).

I have been trying to use this Sql statement(that wont work):
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"

I need to:
1.)using the current EmployeeID (from TblEmployees)
2.) find the same EmployeeID in TblEmpAttendance
3.)Get the date range for the current month and year(valid days currnt mo.)
-these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString

4.) Find the Values associated with the dates and EmployeeIDAre you using Jet or MS-SQL (aka MSDE) as your database engine? They have differences in how they handle dates, and the example you gave would not fly very well in Jet.

-PatP|||Im using jet to connect(through code) to my db.
Can u assist me in how to improve my table format..or whatever I would have to do in order to be able to do this correctly?

All that I did was set up a string format in vb to make sure that the correct amount of characters and the proper syntax was used:
eg... 00/00/0000
In my code I nvr explicity refer to them as a date. Only the user would think that it was a date!

Thanks-Greg S|||I'd try using:SqlString = "SELECT * FROM TblEmpAttendance WHERE #" _
& FirstDay & "# <= Date AND Date <= #" & LastDay & "#"This is only a swag, but I think that it should work.

-PatP|||Thanks I will try that and get back to you. Much appreciated!|||I have a lot more to do than I thought. I did do a date conversion function from within access so it is only displayed as dd/mm/yy format.
Then from vb I used:
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date Bewteen '" & FirstDay & "' AND '" & LastDay & "'"

It seemd to work fine as I can refer to a field but for some reason when I refer to the recordcount property,I always get -1...Seems weird how I can read the info but not get the recordcount!!|||Check the BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp). If you use ADOpenStatic you won't have -1 as a recordcount.

-PatP|||I dont know what you mean by "BOL" but as I am using ADO as a connection it does support bookmarks(not that I believe I need on with the move.bof statement) If this is not what you were getting at could you be more specific?

OK WAIT..I added adOpenStatic to my statement!
now it seems to be working
But I had to put in in the form of:
AdoRecordset.Open SqlString, AdoConnection, adOpenStatic

instead of AdoRecordset.Open (SqlString, AdoConnection), adOpenStatic
like the link you posted...Im still not sure why the perverbial correct way wouldnt work but my way worked anyhow..
THANKS MUCH!|||adOpenStatic, adOpenDynamic, adOpenKeyset, and adOpenForwardOnly are qualifications of the cursor that is being open either on the server or client side (depends on the CursorLocation property). The default (adOpenForwardOnly) will have -1 for RecordCount property.