DMax statement not working

[Sirocco]

Having trouble with what would appear to be a simple VB statement, but
doesn't work.


DMax("[StartTime]", "tblSchedule", "[BeginDate] = " & "[MaxDate]")

where MaxDate is computed first in another DMax statement earlier in the
module. What rule am I breaking? It works if I substitute an actual
date for MaxDate

"#10/20/2003#"
but doesn't work if I use the variable. Is there a rule against using a
criteria that is itself computed in the same module?

Thank you!

 

[Roger Carlson]

Use this instead:

DMax("[StartTime]", "tblSchedule", "[BeginDate] = #" & [MaxDate] & "#")

 

[Sirocco]

I am grateful for your response, and your suggestion worked. I used the
same syntax in a DMax function with 2, rather than 1 criteria, but it
wouldn't work. Can't multiple criteria be used with the DMax function?

Here's the function used with 2 criteria:
MaxStatusCode = DMax("[StatusCode]", "tblStatus", "[CaseCode] = " &
Me![CaseCode] And "[BeginDate] = #" & [MaxDate] & "#")

Use this instead:

DMax("[StartTime]", "tblSchedule", "[BeginDate] = #" & [MaxDate] & "#")

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Having trouble with what would appear to be a simple VB statement, but
doesn't work.


DMax("[StartTime]", "tblSchedule", "[BeginDate] = " & "[MaxDate]")

where MaxDate is computed first in another DMax statement earlier in the
module. What rule am I breaking? It works if I substitute an actual
date for MaxDate

"#10/20/2003#"
but doesn't work if I use the variable. Is there a rule against using a
criteria that is itself computed in the same module?

Thank you!

 

[Roger Carlson]

Certainly. You have a misplaced quote mark:

Here's the function used with 2 criteria:
MaxStatusCode = DMax("[StatusCode]", "tblStatus", "[CaseCode] = " &
Me![CaseCode] " And [BeginDate] = #" & [MaxDate] & "#")

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I am grateful for your response, and your suggestion worked. I used the
same syntax in a DMax function with 2, rather than 1 criteria, but it
wouldn't work. Can't multiple criteria be used with the DMax function?

Here's the function used with 2 criteria:
MaxStatusCode = DMax("[StatusCode]", "tblStatus", "[CaseCode] = " &
Me![CaseCode] And "[BeginDate] = #" & [MaxDate] & "#")

Use this instead:

DMax("[StartTime]", "tblSchedule", "[BeginDate] = #" & [MaxDate] & "#")

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Having trouble with what would appear to be a simple VB statement, but
doesn't work.


DMax("[StartTime]", "tblSchedule", "[BeginDate] = " & "[MaxDate]")

where MaxDate is computed first in another DMax statement earlier in the
module. What rule am I breaking? It works if I substitute an actual
date for MaxDate

"#10/20/2003#"
but doesn't work if I use the variable. Is there a rule against

using

a

 

[Sirocco]

No, I'm missing an ampersand! I inserted an ampersand before the last
"And", then it worked.

Many thanks for your help.

 

[Bruce M. Thompson]

Certainly. You have a misplaced quote mark:

Here's the function used with 2 criteria:
MaxStatusCode = DMax("[StatusCode]", "tblStatus", "[CaseCode] = " &
Me![CaseCode] " And [BeginDate] = #" & [MaxDate] & "#")

....and an ampersand:

MaxStatusCode = DMax("[StatusCode]", "tblStatus", "[CaseCode] = " &
Me![CaseCode] & " And [BeginDate] = #" & [MaxDate] & "#")

and if CaseCode is text, rather than a number:

MaxStatusCode = DMax("[StatusCode]", "tblStatus", "[CaseCode] = """ &
Me![CaseCode] """ And [BeginDate] = #" & [MaxDate] & "#")