Syntax error

[Tony Williams]

I'm getting a syntax error with this code. can anyone see what I am doing
wrong?
Private Sub cmdok_Click()
Dim strform As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.

strform = "frmFDAfindb"
strField = "txtmonthlabel"

strWhere = strField & "#" & Me![txtdate] & "#"


If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND txtcompany = """ & Me.cmbselectcompany & """"
End If

DoCmd.OpenForm strform, acNormal, , strWhere

End Sub

Thanks
tony

 

[Stefan Hoffmann]

hi Tony,

I'm getting a syntax error with this code. can anyone see what I am doing
wrong?

strField = "txtmonthlabel"
strWhere = strField & "#" & Me![txtdate] & "#"

Must be
strWhere = strField & " = #" & Me![txtdate] & "#"


mfG
--> stefan <--

 

[Tony Williams]

Thanks Stefan I've got rid of the syntax error and my form opens but with
nothing there, just a blank form???
Any ideas?
Thanks
Tony

hi Tony,

I'm getting a syntax error with this code. can anyone see what I am doing
wrong?

strField = "txtmonthlabel"
strWhere = strField & "#" & Me![txtdate] & "#"

Must be
strWhere = strField & " = #" & Me![txtdate] & "#"


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Tony,

Thanks Stefan I've got rid of the syntax error and my form opens but with
nothing there, just a blank form???

No data matching your condtion or strWhere is not well-formed.

strField = "txtmonthlabel"
strWhere = strField & "#" & Me![txtdate] & "#"

Must be
strWhere = strField & " = #" & Me![txtdate] & "#"

What does a
MsgBox strWhere
say?


mfG
--> stefan <--

 

[Tony Williams]

Stefan I've added a check for the date and my code is this
Private Sub cmdok_Click()
Dim strform As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.


strform = "frmFDAfindb"
strField = "txtmonthlabel"


Me.txtdate.SetFocus
If Nz(Me.txtdate.Text) <> "" Then
strWhere = strField & " = #" & Me![txtdate] & "#"
If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND txtcompany = """ & Me.cmbselectcompany &
""""
End If
DoCmd.OpenForm strform, acNormal, , strWhere
Else
MsgBox "date needed", vbOKOnly
Me.txtdate.SetFocus
End If


End Sub

Any ideas?
Thanks for your help with this I've been struggling for the last three
hours!
Cheers
Tony

hi Tony,

Thanks Stefan I've got rid of the syntax error and my form opens but with
nothing there, just a blank form???

No data matching your condtion or strWhere is not well-formed.

strField = "txtmonthlabel"
strWhere = strField & "#" & Me![txtdate] & "#"
Must be
strWhere = strField & " = #" & Me![txtdate] & "#"

What does a
MsgBox strWhere
say?


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Tony,

If Not IsNull(Me.cmbselectcompany) Then
strWhere = strWhere & " AND txtcompany = """ & Me.cmbselectcompany &
""""
End If

Place here a
MsgBox strWhere
to display your condition. I assume it is not well-formed. Can you post
such a condition string?

DoCmd.OpenForm strform, acNormal, , strWhere

mfG
--> stefan <--

 

[Tony Williams]

Stefan
If I just put a date in my form the Msgbox says

txtmonthlabel = #01/12/06# and then the form opens to a blank.

If I put a date and a company the msgbox says
txtmonthlabel = #01/12/06# AND txtcompany = #01/09/95# and then the form
opens to a blank

Very odd? Don't know where the last date comes from??? Also the date format
on the form is mmmm yyyy

Thanks for sticking with me!
Tony

 

[Tony Williams]

Sorry Stefan, I had my combo box set to the wrong column.
The message box now says
txtmonthlabel = #01/12/06# AND txtcompany = "CompanyA" and then the form
opens to a blank
Cheers
Tony

 

[Tony Williams]

Looks like the problem lies with the date format???
Tony

 

[Stefan Hoffmann]

hi Tony,

txtmonthlabel = #01/12/06# and then the form opens to a blank.

Ah, txtmonthlabel is the name of your control on your form, not of the
field in your table, isn't it?

Very odd? Don't know where the last date comes from???

Is Me.cmbselectcompany the correct control?

Also the date format on the form is mmmm yyyy

This is just a display format. It does not apply to your data. So
#01/09/95# is the correct default date format.


mfG
--> stefan <--

 

[Tony Williams]

Stefan, the name of the control on the form and the field in the table are
both txtmonthlabel! Is that a problem?
See subsequent email about company
Are we getting anywhere?
Cheers
Tony

 

[Stefan Hoffmann]

hi Tony,

Stefan, the name of the control on the form and the field in the table are
both txtmonthlabel! Is that a problem?

It is called txtmonthlabel in the table, which data type has this field?
Is it a Date/Time field or a Text field?

Are we getting anywhere?

I'm not sure. Your code is correct as far as i can see.


mfG
--> stefan <--

 

[Tony Williams]

Stefan I added this line from some other code I had

Const conDateFormat = "\#mmmm\/yyyy\#"
and change the strwhere line to this
strWhere = strField & " = " & Format(Me.txtdate, conDateFormat)
and it worked!!!!
Don't know why but it did. Any ideas? I like to learn from my mistakes!
Thanks for all your help
Tony

 

[Stefan Hoffmann]

hi Tony,

Stefan I added this line from some other code I had

Const conDateFormat = "\#mmmm\/yyyy\#"
and change the strwhere line to this
strWhere = strField & " = " & Format(Me.txtdate, conDateFormat)
and it worked!!!!
Don't know why but it did. Any ideas? I like to learn from my mistakes!

The name of your field txtmonthlabel indicates that the data type is not
a Date/Time value, but a normal Text field. The #01/02/95# syntax only
works for Date/Time fields.


mfG
--> stefan <--

 

[Tony Williams]

Stefan that's odd because in my table it's shown as Date/Time in the Data
Type with a format of mmmm yyyy I confess I don't understand this???
Thanks
Tony

 

[Stefan Hoffmann]

hi Tony,

Stefan that's odd because in my table it's shown as Date/Time in the Data
Type with a format of mmmm yyyy I confess I don't understand this???

strField & " = " & Format(Now(), "\#mmmm\/yyyy\#")

gives us "txtmonthlabel = #June/2006#". This will match _all_ days in June.
The prior formmatted condition was "txtmonthlabel = #02/06/06#", which
will only return _one_ day, if available. And i assume there was no data
for that special day.


mfG
--> stefan <--

 

[Tony Williams]

Thanks Stefan I understand now. Sometimes this 62 year old brain of mine
takes a little time for it to get into gear.But I get there in the end!!!!
Must go back to school to learn this instead of trying to teach myself
Tony

 

[Douglas J Steele]

hi Tony,



strField & " = " & Format(Now(), "\#mmmm\/yyyy\#")

gives us "txtmonthlabel = #June/2006#". This will match _all_ days in June.
The prior formmatted condition was "txtmonthlabel = #02/06/06#", which
will only return _one_ day, if available. And i assume there was no data
for that special day.

I don't believe that's correct, Stefan.

Under the covers, a date is an 8 byte floating point number (the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
while the decimal portion represents the time as a fraction of a day). If
you simply use #June/2006#, Access is going to try and convert that to a
proper date. Go the Immediate Window and see what happens when you type

?CDate(#June/2006#)

Access makes an assumption that the day is 1: it has to do this, since it
can't represent a date otherwise.

 

[Stefan Hoffmann]

hi douglas,

I don't believe that's correct, Stefan.
Access makes an assumption that the day is 1: it has to do this, since it
can't represent a date otherwise.

My fault, that was what i had in mind, but i wasn't able to write it
down properly.


mfG
--> stefan <--