Syntax problem

[AltaEgo]

Hi,

Its getting late down here in Oz. My focus departed hours ago.

Can someone tell me why the follow DMax statement is returning error 3075
syntax error missing operator in expression '[Xraydate] > and [Line] = 3?


maxNum = DMax("[CountFinish]", "tblXrayStats", "[xraydate] > " & strDate &
" And " & "[Line] =" & Forms!frmXrayStats!Line.Value)

strDate evaluates as date wrapped with '#'. For example today shows as
'#15/6/2008#' (without quotes).

I suspect I am close - just can't see it.

 

[Douglas J. Steele]

The error message implies that strDate doesn't have a value. Perhaps your
code is setting some other variable than strDate? What's the preceding code
look like?

BTW, even though you may have your regional settings such that the Short
Date format is dd/mm/yyyy, you must use mm/dd/yyyy format, or an unambiguous
one. Access will ALWAYS treat #12/6/2008# as 06 Dec, 2008.

For more information, see Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[AltaEgo]

Thank you. I was bitten and learned about the illogical ) mm/dd/yyyy date
format in VBA many years ago.

A clearer head has me seeing it easily this morning. Fixed in seconds after
you questioned strDate.
Guess who missed the 'As String' on the Dim statement (big sheepish grin).

Dim strDate As String
strDate = "#" & Format(Date - 7, "dd/mm/yyyy") & "#"

--
Steve
(must remember Pascal coding discipline)

The error message implies that strDate doesn't have a value. Perhaps your
code is setting some other variable than strDate? What's the preceding
code look like?

BTW, even though you may have your regional settings such that the Short
Date format is dd/mm/yyyy, you must use mm/dd/yyyy format, or an
unambiguous one. Access will ALWAYS treat #12/6/2008# as 06 Dec, 2008.

For more information, see Allen Browne's "International Dates in Access"
at http://allenbrowne.com/ser-36.html or what I had in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access"
newsletter. (The column and accompanying database can be downloaded for
free at http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

Its getting late down here in Oz. My focus departed hours ago.

Can someone tell me why the follow DMax statement is returning error 3075
syntax error missing operator in expression '[Xraydate] > and [Line] = 3?


maxNum = DMax("[CountFinish]", "tblXrayStats", "[xraydate] > " & strDate
& " And " & "[Line] =" & Forms!frmXrayStats!Line.Value)

strDate evaluates as date wrapped with '#'. For example today shows as
'#15/6/2008#' (without quotes).

I suspect I am close - just can't see it.

 

[Douglas J. Steele]

You say you were bitten about mm/dd/yyyy, yet you're not using it!

That needs to be:

Dim strDate As String

strDate = "#" & Format(Date - 7, "mm/dd/yyyy") & "#"

or it won't work next month....

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you. I was bitten and learned about the illogical ) mm/dd/yyyy
date format in VBA many years ago.

A clearer head has me seeing it easily this morning. Fixed in seconds
after you questioned strDate.
Guess who missed the 'As String' on the Dim statement (big sheepish
grin).

Dim strDate As String
strDate = "#" & Format(Date - 7, "dd/mm/yyyy") & "#"

--
Steve
(must remember Pascal coding discipline)

The error message implies that strDate doesn't have a value. Perhaps your
code is setting some other variable than strDate? What's the preceding
code look like?

BTW, even though you may have your regional settings such that the Short
Date format is dd/mm/yyyy, you must use mm/dd/yyyy format, or an
unambiguous one. Access will ALWAYS treat #12/6/2008# as 06 Dec, 2008.

For more information, see Allen Browne's "International Dates in Access"
at http://allenbrowne.com/ser-36.html or what I had in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access"
newsletter. (The column and accompanying database can be downloaded for
free at http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

Its getting late down here in Oz. My focus departed hours ago.

Can someone tell me why the follow DMax statement is returning error
3075 syntax error missing operator in expression '[Xraydate] > and
[Line] = 3?


maxNum = DMax("[CountFinish]", "tblXrayStats", "[xraydate] > " &
strDate & " And " & "[Line] =" & Forms!frmXrayStats!Line.Value)

strDate evaluates as date wrapped with '#'. For example today shows as
'#15/6/2008#' (without quotes).

I suspect I am close - just can't see it.

 

[AltaEgo]

Forgive me. I've been exclusively programming Excel for about eight years
and forgot more about Access than I ever learned!

It is working now 16/6/2008 Bearing in mind it is comparing to a table
formatted dd/mm/yyyy, should it not continue to work?

I need to get this right today. Tomorrow is my retirement day. Really.

--
Steve

You say you were bitten about mm/dd/yyyy, yet you're not using it!

That needs to be:

Dim strDate As String

strDate = "#" & Format(Date - 7, "mm/dd/yyyy") & "#"

or it won't work next month....

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you. I was bitten and learned about the illogical ) mm/dd/yyyy
date format in VBA many years ago.

A clearer head has me seeing it easily this morning. Fixed in seconds
after you questioned strDate.
Guess who missed the 'As String' on the Dim statement (big sheepish
grin).

Dim strDate As String
strDate = "#" & Format(Date - 7, "dd/mm/yyyy") & "#"

--
Steve
(must remember Pascal coding discipline)

The error message implies that strDate doesn't have a value. Perhaps
your code is setting some other variable than strDate? What's the
preceding code look like?

BTW, even though you may have your regional settings such that the Short
Date format is dd/mm/yyyy, you must use mm/dd/yyyy format, or an
unambiguous one. Access will ALWAYS treat #12/6/2008# as 06 Dec, 2008.

For more information, see Allen Browne's "International Dates in Access"
at http://allenbrowne.com/ser-36.html or what I had in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access"
newsletter. (The column and accompanying database can be downloaded for
free at http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

Its getting late down here in Oz. My focus departed hours ago.

Can someone tell me why the follow DMax statement is returning error
3075 syntax error missing operator in expression '[Xraydate] > and
[Line] = 3?


maxNum = DMax("[CountFinish]", "tblXrayStats", "[xraydate] > " &
strDate & " And " & "[Line] =" & Forms!frmXrayStats!Line.Value)

strDate evaluates as date wrapped with '#'. For example today shows as
'#15/6/2008#' (without quotes).

I suspect I am close - just can't see it.

 

[Douglas J. Steele]

The only reason 16/6/2008 is interpretted correctly (as 16 Jun, 2008) is
because there isn't a 16th month.

dd/mm/yyyy will continue to work until 30 June, 2008. Come July 1
(1/7/2008), the date will be interpretted as 7 Jan, 2008.

Read the articles I cited earlier.

Best wishes for your retirement!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Forgive me. I've been exclusively programming Excel for about eight years
and forgot more about Access than I ever learned!

It is working now 16/6/2008 Bearing in mind it is comparing to a table
formatted dd/mm/yyyy, should it not continue to work?

I need to get this right today. Tomorrow is my retirement day. Really.

--
Steve

You say you were bitten about mm/dd/yyyy, yet you're not using it!

That needs to be:

Dim strDate As String

strDate = "#" & Format(Date - 7, "mm/dd/yyyy") & "#"

or it won't work next month....

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you. I was bitten and learned about the illogical ) mm/dd/yyyy
date format in VBA many years ago.

A clearer head has me seeing it easily this morning. Fixed in seconds
after you questioned strDate.
Guess who missed the 'As String' on the Dim statement (big sheepish
grin).

Dim strDate As String
strDate = "#" & Format(Date - 7, "dd/mm/yyyy") & "#"

--
Steve
(must remember Pascal coding discipline)

The error message implies that strDate doesn't have a value. Perhaps
your code is setting some other variable than strDate? What's the
preceding code look like?

BTW, even though you may have your regional settings such that the
Short Date format is dd/mm/yyyy, you must use mm/dd/yyyy format, or an
unambiguous one. Access will ALWAYS treat #12/6/2008# as 06 Dec, 2008.

For more information, see Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I had in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be
downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

Its getting late down here in Oz. My focus departed hours ago.

Can someone tell me why the follow DMax statement is returning error
3075 syntax error missing operator in expression '[Xraydate] > and
[Line] = 3?


maxNum = DMax("[CountFinish]", "tblXrayStats", "[xraydate] > " &
strDate & " And " & "[Line] =" & Forms!frmXrayStats!Line.Value)

strDate evaluates as date wrapped with '#'. For example today shows as
'#15/6/2008#' (without quotes).

I suspect I am close - just can't see it.

 

[AltaEgo]

Thank you. My PC must have a friendlier Windows date than the one at work
because it flopped yesterday at work using dd/mm/yyyy. The alternative date
format ... {you know this already}.

--
Steve

The only reason 16/6/2008 is interpretted correctly (as 16 Jun, 2008) is
because there isn't a 16th month.

dd/mm/yyyy will continue to work until 30 June, 2008. Come July 1
(1/7/2008), the date will be interpretted as 7 Jan, 2008.

Read the articles I cited earlier.

Best wishes for your retirement!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Forgive me. I've been exclusively programming Excel for about eight years
and forgot more about Access than I ever learned!

It is working now 16/6/2008 Bearing in mind it is comparing to a table
formatted dd/mm/yyyy, should it not continue to work?

I need to get this right today. Tomorrow is my retirement day. Really.

--
Steve

You say you were bitten about mm/dd/yyyy, yet you're not using it!

That needs to be:

Dim strDate As String

strDate = "#" & Format(Date - 7, "mm/dd/yyyy") & "#"

or it won't work next month....

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you. I was bitten and learned about the illogical ) mm/dd/yyyy
date format in VBA many years ago.

A clearer head has me seeing it easily this morning. Fixed in seconds
after you questioned strDate.
Guess who missed the 'As String' on the Dim statement (big sheepish
grin).

Dim strDate As String
strDate = "#" & Format(Date - 7, "dd/mm/yyyy") & "#"

--
Steve
(must remember Pascal coding discipline)

message The error message implies that strDate doesn't have a value. Perhaps
your code is setting some other variable than strDate? What's the
preceding code look like?

BTW, even though you may have your regional settings such that the
Short Date format is dd/mm/yyyy, you must use mm/dd/yyyy format, or an
unambiguous one. Access will ALWAYS treat #12/6/2008# as 06 Dec, 2008.

For more information, see Allen Browne's "International Dates in
Access" at http://allenbrowne.com/ser-36.html or what I had in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be
downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

Its getting late down here in Oz. My focus departed hours ago.

Can someone tell me why the follow DMax statement is returning error
3075 syntax error missing operator in expression '[Xraydate] > and
[Line] = 3?


maxNum = DMax("[CountFinish]", "tblXrayStats", "[xraydate] > " &
strDate & " And " & "[Line] =" & Forms!frmXrayStats!Line.Value)

strDate evaluates as date wrapped with '#'. For example today shows
as '#15/6/2008#' (without quotes).

I suspect I am close - just can't see it.