Queries and reports

[FireViking]

Hi,
I have to create a monthly report for the program I have written. I need to run a query through code to show information between specific dates. I have a field named "DatesIssued" in my table. I need to know the correct syntax for the query. I created a simple query using the query wizard and for the criteria used "Between [Beginning Date:] And [End Date:]" then pasted the SQL into the code but obviously that wont work.
Can some one help please.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = currentDB
Set rs = db.openrecordset("Select * from HERE IS WHERE I NEED HELP)

Thanks and cheers.

 

[Oppenhiemer]

Hmm.. Access 2000 by the look. Ok - I have no specific answer for you - but here is an overview of what the final (SQL) statement may look like :

Dim one_date as Date
Dim another_date as Date

sql = "SELECT * FROM TABLENAME WHERE DAteIssued BETWEEN one_date AND another_date"

Just set the variables "from_date" and "another_date" to the date ranges before you run the query and that should work (if you have problems - then try defining the dates as string types not date types.)

Hope this helps..

 

[Tiso]

The variables have to be outside the SQL string and dates have to be included within "#", so the correct sintax for the SQL string is:

"SELECT * FROM [TABLENAME] WHERE ((([TABLENAME].DateIssued) BETWEEN #" & [start date] & "# AND #" & [end date] & "#))"


where [start date] and [end date] are string variables, i.e in format "mm/dd/yy"

 

[FireViking]

Hi,
Tiso's syntax worked fine but with one small hitch.

The recordset is only returning 1 record when i have 4 within the period of time.

The code is as follows
Dim etc, etc

StartDate1 = "01/05/00"
EndDate1 = "01/08/01"
StartDate = Format(StartDate1, "dd/mm/yy&quot
EndDate = Format(EndDate1, "dd/mm/yy&quot

Set db = CurrentDB
Set rs = db.openrecordset("Select * From tblInfringement WHERE (((tblInfringement.DateOfIssue) BETWEEN " _
& "#" & [StartDate] & "# AND #" & [EndDate] & "#))&quot

rs.movefirst
Do While Not rs.EOF
count = count+1
rs.movenext
Loop

When I run the query it only returns 1 record. There should be 4

Thanks for your help.
Cheers

 

[GeekGirlau]

SQL reads dates in American format (mm/dd/yy). If you are using dates entered on a form, you need to convert the dates first.

Dim db as Database
Dim rs as Recordset
Dim strStartDate as String
Dim strEndDate as String
Dim strSQL as String
Dim count as Integer


strStartDate = Month(Me.txtStartDate) & "/" & _
Day(Me.txtStartDate) & "/" & _
Year(me.txtStartDate)
strEndDate = Month(Me.txtEndDate) & "/" & _
Day(Me.txtEndDate) & "/" & _
Year(me.txtEndDate)

strSQL = "Select * From tblInfringement WHERE "
strSQL = strSQL & DateOfIssue BETWEEN #"
strSQL = strSQL & strStartDate & "# AND #"
strSQL = strSQL & strEndDate & "#"

Set db = CurrentDB
Set rs = db.openrecordset(strSQL)

rs.movefirst

Do While Not rs.EOF
count = count+1
rs.movenext
Loop

rs.Close
db.Close

 

[FireViking]

Hi,
Thank you GeekGirlau

Changing the format of the date was the key, it works fine now. The previous query produced a result but only showed 1 record(first record).In my opinion it should have shown nothing.

Thanks heaps

 

[jode]

For future reference I have found that it is easier to format the date to "dd-mmm-yyyy", then there is no confusion at all as to whether it is American or English. This saves re-creating the date yourself!