Access Database

Hey all.. I have this one report that has two fields in it.. Those two fields are dtmArrive (for arrival date) and dtmDepart (for departure date).. I am trying to have a form that calls this report by the date that a user chooses.. But that date needs to fall between dtmArrive and dtmDepart.. Its called a stay over report.. So if the user puts in a date and it finds that someone is going to be staying over on that date it will show this report.. This is the small code that I had before.. Please help..

Private Sub btnOK_Click() ' FJN 3/7/2001

On Error GoTo Err_btnOK_Click

Dim txtResp1 As String

txtResp1 = txtResp.Value

DoCmd.OpenReport "rptStayOver", [acViewPreview], , "dtmArrive <> # & dtmDepart <> #" & txtResp1 & "#"
DoCmd.Close acForm, "frmStayOver"

Err_btnOK_Click:

Select Case Err

Case 2501 'report cancelled, just go on
Exit Sub
Case 3075 'syntax error
MsgBox "You have entered an invalid date! ", vbCritical, "Please try again."
Exit Sub
Case Else
MsgBox "Error in btnOK_Click, error is " & Err & " - " & Err.Description, vbCritical, gstrDAHMSG
Exit Sub
End Select
End Sub

I have my question with this line:

DoCmd.OpenReport "rptStayOver", [acViewPreview], , "dtmArrive <> # & dtmDepart <> #" & txtResp1 & "#"

When you check the last parameter, it evaluates as a string containing this (if txtResp1 = "01/01/2001"):
"dtmArrive <> # & dtmDepart <> #01/01/2001#"
If dtmArrive and dtmDepart are fields in the database, you didn't suplly a value, also, the syntax isn't quite right.
This should give all the records between two dates

"dtmArrive > #" & ArriveDate & "# and dtmDepart < #" & DepartDate & "#"

where Arrivedate and Departdate are two strings/dates containing the begin and end date.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook