Date Picker 5

[diggerbob]

I have used a date picker which uses a calendar on my form for sometime now and have no problem with it. I copied this to a new database that I am starting for someone else at work but every time I try to run it, I get the following error message: Run-Time Error '3075', Syntax error (missing operator) in query expression (Received Date Between #02/01/2008 And #02/18/2008#). I have even made another form from scratch and I still get the same error. The only thing I am doing differently is retreiving their table data from an import from excel.

 

[PHV]

[!][[/!]Received Date[!]][/!] Between #02/01/2008[!]#[/!] And #02/18/2008#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[diggerbob]

PH,
I should have explained my problem more clearly. I cannot find where this query expression is. I have only one table, I did a query from this table, and then did a report from that query. That's all I've done and the date picker should work, but doesn't.

 

[ck1999]

What is the sql code for your query?

ck1999

 

[diggerbob]

Actually, the error is popping up in Visual Basics

 

[boblarson]

It is likely that PHV's response is correct and that you don't have the correct delimiters in your query.

However, you say that you are using a datepicker? So, the question I have is how did you refer to the datepicker in the query?

Post the SQL of the query if you can. You can get at it by opening the query in Design View and then going up to VIEW and select SQL. Copy and paste it here.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[diggerbob]

Bob, this is all of my query and after this I have copied and pasted Visual Basics codes from my date picker form and if this doesn't help, I will send my database up to my web space this evening and you can review it:

Query:

SELECT [Accrual Table].[Vendor Name], [Accrual Table].[PO No], [Accrual Table].[Account Code], [Accrual Table].[Date Received], [Accrual Table].[AES Item No], [Accrual Table].[Purchase Order Description], [Accrual Table].[Cost Center], [Accrual Table].[Receive To ID], [Accrual Table].[Qty Received (UOP)], [Accrual Table].[Original Unit Cost], [Accrual Table]![Qty Received (UOP)]*[Accrual Table]![Original Unit Cost] AS [Received Total], [Accrual Table].[Item Total], [Item Total]-[Received Total] AS [Amount Not Received]
FROM [Accrual Table];



Visual Basics:

Private Sub cboStartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Set cboOriginator = cboStartDate
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(cboOriginator) Then
ocxCalendar.Value = cboOriginator.Value
Else
ocxCalendar.Value = Date
End If
End Sub

Private Sub ocxCalendar_Click()
cboOriginator.Value = ocxCalendar.Value
cboOriginator.SetFocus
ocxCalendar.Visible = False
Set cboOriginator = Nothing

End Sub

Private Sub OK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "End Of Month Accrual"
strField = "Received Date"

If IsNull(Me.cboStartDate) Then
If Not IsNull(Me.cboEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.cboEndDate, conDateFormat)
End If
Else
If IsNull(Me.cboStartDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.cboStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.cboStartDate, conDateFormat) _
& " And " & Format(Me.cboEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub
Private Sub Printer_Button_Click()
On Error GoTo Err_Printer_Button_Click

Dim stDocName As String 'Name of report to print.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"


stDocName = "End Of Month Accrual"
strField = "Received Date"

If IsNull(Me.cboStartDate) Then
If Not IsNull(Me.cboEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.cboEndDate, conDateFormat)
End If
Else
If IsNull(Me.cboStartDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.cboStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.cboStartDate, conDateFormat) _
& " And " & Format(Me.cboEndDate, conDateFormat)
End If
End If

DoCmd.OpenReport stDocName, acNormal, , strWhere

Exit_Printer_Button_Click:
Exit Sub

Err_Printer_Button_Click:
MsgBox Err.Description
Resume Exit_Printer_Button_Click

End Sub

 

[ck1999]

Looks like this is your error

strWhere = strField & " Between " & Format(Me.cboStartDate, conDateFormat) _
& " And " & Format(Me.cboEndDate, conDateFormat)

try

strWhere = strField & " Between #" & Format(Me.cboStartDate, conDateFormat) _
& #" And "# & Format(Me.cboEndDate, conDateFormat) & "#"

this occurs 2 times

ck1999

 

[PHV]

Replace this:
strField = "Received Date"
with this:
strField = "[Received Date]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[diggerbob]

ck1999,
I tried the change but I get compile error messages at the # signs around the word #"And"#

PH,
Tried your suggestion but doesn't work either.

 

[ck1999]

Try this

strWhere = strField & " Between #" & Format(Me.cboStartDate, conDateFormat) _
& "# And #" & Format(Me.cboEndDate, conDateFormat) & "#"

ck1999

 

[diggerbob]

ck1999,

No, it still doesn't work, it just doubles the # sign in the error message. I hope by tonight, getting this up and available on line but will let you know.

Thanks,
Bob K

 

[ck1999]

Sorry I had not realized you had the # in your formatting step

try this to see it works

strWhere = "[Received Date] Between " & Format(Me.cboStartDate, conDateFormat) _
& " And " & Format(Me.cboEndDate, conDateFormat)

besidest this I am out of ideas.

ck1999

 

[ProgramError]

Const conDateFormat = "\#mm\/dd\/yyyy\#"

shouldn't this be

Const conDateFormat = "mm/dd/yyyy"

I'd do a debug on the expression
Format(Me.cboStartDate, conDateFormat)
and see what it throws up


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[ProgramError]

PVH's syntax is correct too and needs including....


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[diggerbob]

ck1999,
I know you said that you would be out of ideas but I would like you to take a look at the database. I have attached the url where it can be found. If that doesn't work I appreciate your help

 

[diggerbob]

Ian,
I will give your ideas a shot when I get to work tomorrow.
Thanks
Bob K

 

[ck1999]

Do not kick your self to hard but ...

strField = "Received Date"

should be

strField = "[Date Received]"



ck1999

 

[MajP]

strField = "[Date Received]

 

[ck1999]

Also upon further observations

 If IsNull(Me.cboStartDate) Then
        If Not IsNull(Me.cboEndDate) Then   'End date, but no start.
            strWhere = strField & " < " & Format(Me.cboEndDate, conDateFormat)
        End If
    Else
        If IsNull(Me.cboStartDate) Then       'Start date, but no End.
            strWhere = strField & " > " & Format(Me.cboStartDate, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.cboStartDate, conDateFormat) _
                & " And " & Format(Me.cboEndDate, conDateFormat)
        End If
    End If

i think you meant it to be

 If IsNull(Me.cboStartDate) Then
        If Not IsNull(Me.cboEndDate) Then   'End date, but no start.
            strWhere = strField & " < " & Format(Me.cboEndDate, conDateFormat)
        End If
    Else
        If IsNull(Me.cboendDate) Then       'Start date, but no End.
            strWhere = strField & " > " & Format(Me.cboStartDate, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.cboStartDate, conDateFormat) _
                & " And " & Format(Me.cboEndDate, conDateFormat)
        End If
    End If

You get an error if you use your code

ck1999