Why can I not enter a valid date for this code...

  • Thread starter Thread starter Kelvin Beaton
  • Start date Start date
K

Kelvin Beaton

I have a form that I enter a date into and then press a buton to run the
code below.

No date seem to be valid for this section of code to no display the message,
----------------------------------------------------------------------------------------------------
ElseIf Me.ImportDate > DMax([ImportDate], "qry_PSI_importdate_max") Then
MsgBox "You have already imported PSI data for this date"
----------------------------------------------------------------------------------------------------
I can enter any date and it always says "You have already imported PSI data
for this date".
I've entered dates a year from now and into the past, but no difference.

I can look in the query and see the MAX date, so I know what that is.

Can someone see what is wrong with this code?

Thanks

Kelvin


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim stDocName As String
If IsNull([ImportDate]) Then
MsgBox "You must enter an Import date."
ElseIf Me.ImportDate > DMax([ImportDate], "qry_PSI_importdate_max")
Then
MsgBox "You have already imported PSI data for this date"
Else
DoCmd.RunMacro "mac_ImportPSIData", , ""
MsgBox "PSI Data Imported Successfully."
DoCmd.Close
Exit_Command8_Click:
Exit Sub
Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click
End If
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
Kelvin,
Try to add quotes around [ImportDate]

You want the DMax part of that line of code to read:
DMax("[ImportDate]", "qry_PSI_importdate_max")

Good luck,
Mattias

From: Kelvin Beaton, on 4/26/2007 2:16 PM:
 
Thank you! Thank you!

Amazing what a could of quotes will do.
What rule or what ever would make one know that those quotes were needed?

Thanks a million!

Kelvin

Mattias Jonsson said:
Kelvin,
Try to add quotes around [ImportDate]

You want the DMax part of that line of code to read:
DMax("[ImportDate]", "qry_PSI_importdate_max")

Good luck,
Mattias

From: Kelvin Beaton, on 4/26/2007 2:16 PM:
I have a form that I enter a date into and then press a buton to run the
code below.

No date seem to be valid for this section of code to no display the
message,
----------------------------------------------------------------------------------------------------
ElseIf Me.ImportDate > DMax([ImportDate], "qry_PSI_importdate_max") Then
MsgBox "You have already imported PSI data for this date"
----------------------------------------------------------------------------------------------------
I can enter any date and it always says "You have already imported PSI
data for this date".
I've entered dates a year from now and into the past, but no difference.

I can look in the query and see the MAX date, so I know what that is.

Can someone see what is wrong with this code?

Thanks

Kelvin


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim stDocName As String
If IsNull([ImportDate]) Then
MsgBox "You must enter an Import date."
ElseIf Me.ImportDate > DMax([ImportDate], "qry_PSI_importdate_max")
Then
MsgBox "You have already imported PSI data for this date"
Else
DoCmd.RunMacro "mac_ImportPSIData", , ""
MsgBox "PSI Data Imported Successfully."
DoCmd.Close
Exit_Command8_Click:
Exit Sub
Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click
End If
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Click to expand...
Click to expand...
 
Kelvin,
This is probably beyond me to explain. It has to do with the difference
between a string and a variable. You might make a simple test database
where you play around with what happens if you use quotes (or not), and
try to figure out why.

Take a look at this website to get you started:
http://allenbrowne.com/casu-07.html

Once you learn though, it will help you _a lot_, so it's probably worth
the initial time investment.

Here's another resource that will give you some background info:
http://en.wikipedia.org/wiki/String_literal

Good luck,
Mattias

From: Kelvin Beaton, on 4/26/2007 3:33 PM:
 
thanks I'll take a look...

Kelvin

Mattias Jonsson said:
Kelvin,
This is probably beyond me to explain. It has to do with the difference
between a string and a variable. You might make a simple test database
where you play around with what happens if you use quotes (or not), and
try to figure out why.

Take a look at this website to get you started:
http://allenbrowne.com/casu-07.html

Once you learn though, it will help you _a lot_, so it's probably worth
the initial time investment.

Here's another resource that will give you some background info:
http://en.wikipedia.org/wiki/String_literal

Good luck,
Mattias

From: Kelvin Beaton, on 4/26/2007 3:33 PM:
Click to expand...
 
Thank you! Thank you!

Amazing what a could of quotes will do.
What rule or what ever would make one know that those quotes were needed?
Click to expand...

All of the Domain functions (DLookUp, DMax, etc.) take three arguments - and
all three arguments must be string values.

The first is a string which evaluates to the name of the field to look up, or
sum, or find the maximum, etc.

The second is the name of a Table or a Query in which to look.

The third (optional) argument is another string, which forms a valid SQL WHERE
clause to select which record (or records) to retrieve from the table or query
in the second argument.

Note that these could be string constants - e.g.

=DMax("[ImportDate]", "qry_PSI_importdate_max")

or variables - e.g.

Dim strQueryname As String

strQueryname = "qry_PSI_importdate_max"
....
something = DMax("[ImportDate]", strQueryname)

or the string could be constructed by appending bits of string constants and
variables together - but in any case it must be a string.

John W. Vinson [MVP]
 
Allen Browne's explanation of quotes is very helpful, as are quite a few
things at his web site, but John Vinson's explanation in this thread of
quotes in domain functions such as DMax is more to the point of the current
issue.
 
Back
Top