Date/time Poblem 2

[lars7]

I have a qry that will not run when I put a date in the criteria even thou it's a date/time field. But when I change the field to text in the table it works ok. Does anyone know why this is happening and if so how do I get it to work? I really need to keep it as a date/time field.

 

[dhookom]

What do you mean by "will not run"? Have you tried a date range rather than a date? Does your date field contain a time portion in the values? What is the date format in your location?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lars7]

From a form with three combo boxes I can run the query. If I select the other two criteria then the query runs fine e.g.: trust; Paynumber: These combo boxes can have a value in them or not the query will still run. But when I put a value in the third combo: dateprocessed, which is a short date field I get an error message saying "you cancelled the previous operation" but when I changed it to a text field it worked fine. Here is the code I’ve used:

Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strtrust As String
Dim strpaynumber As String
Dim strDateprocessed As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryStaffListQuery") Then
Set qdf = db.CreateQueryDef("qryStaffListQuery")
Else
Set qdf = db.QueryDefs("qryStaffListQuery")
End If
' Get the values from the combo boxes
If IsNull(Me.cbotrust.Value) Then
strtrust = " Like '*' "
Else
strtrust = "='" & Me.cbotrust.Value & "' "
End If
If IsNull(Me.Cbopaynumber.Value) Then
strpaynumber = " Like '*' "
Else
strpaynumber = "='" & Me.Cbopaynumber.Value & "' "
End If
If IsNull(Me.CboDate.Value) Then
strDateprocessed = " Like '*' "
Else
strDateprocessed = "='" & Me.CboDate.Value & "' "
End If
' Build the SQL string
strSQL = "SELECT tbltrustStaff.* " & _
"FROM tbltrustStaff " & _
"WHERE tbltrustStaff.trust" & strtrust & _
"AND tbltrustStaff.paynumber " & strpaynumber & _
"AND tbltrustStaff.Dateprocessed" & strDateprocessed & _
"ORDER BY tbltrustStaff.trust,tbltrustStaff.Dateprocessed;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "qryStaffListQuery"
End If
' Open the query
DoCmd.OpenQuery "qryStaffListQuery"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub

There's also the QueryExists module I’ll show you if it will help.

 

[Golom]

Date fields need different delimiters.

    If IsNull(Me.CboDate.Value) Then
        strDateprocessed = " > #01/01/1900# "
    Else
        strDateprocessed = "=#" & Me.CboDate.Value & "# "
    End If

Assuming that all your dates are after Jan 1, 1900.

 

[lars7]

Thanks Golom that worked.

 

[PHV]

You may try something like this:
' Get the values from the combo boxes
strWhere = ""
If Trim(Me!cbotrust & "") <> "" Then
strWhere = strWhere & "AND trust='" & Me!cbotrust & "' "
End If
If Trim(Me!Cbopaynumber & "") <> "" Then
strWhere = strWhere & "AND paynumber='" & Me!Cbopaynumber & "' "
End If
If IsDate(Me!CboDate) Then
strWhere = strWhere & "AND Dateprocessed=#" & Format(Me!CboDate, "yyyy-mm-dd") & "# "
End If
If Len(strWhere) Then strWhere = "WHERE" & Mid(strWhere, 4)
' Build the SQL string
strSQL = "SELECT * FROM tbltrustStaff " & _
strWhere & " ORDER BY trust, Dateprocessed"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Thanks PHV that's worked too.

Could you advise me how to change this from a Select to a Delete Query? If I change the Query manually it just reverts back to a select one when it runs.

 

[PHV]

how to change this from a Select to a Delete Query
Replace this:
strSQL = "SELECT * FROM tbltrustStaff " & _
strWhere & " ORDER BY trust, Dateprocessed"
with this:
strSQL = "DELETE FROM tbltrustStaff " & strWhere

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Thanks PHV it's working fine now.