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

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
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 


0
Kelvin
4/26/2007 6:16:45 PM
access 16762 articles. 3 followers. Follow

6 Replies
580 Views

Similar Articles

[PageSpeed] 48

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
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
> 
> 
0
Mattias
4/26/2007 6:29:21 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" <news@ett-remove-idesystem.se> wrote in message 
news:Olw5UDDiHHA.4228@TK2MSFTNGP03.phx.gbl...
> 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
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 


0
Kelvin
4/26/2007 7:33:36 PM
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:
> 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
0
Mattias
4/26/2007 7:47:07 PM
thanks I'll take a look...

Kelvin

"Mattias Jonsson" <news@ett-remove-idesystem.se> wrote in message 
news:u25XyuDiHHA.4872@TK2MSFTNGP03.phx.gbl...
> 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:
>> 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 


0
Kelvin
4/26/2007 7:59:15 PM
On Thu, 26 Apr 2007 14:33:36 -0500, "Kelvin Beaton" <kelvin at mccsa dot com>
wrote:

>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?

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]
0
John
4/26/2007 8:09:32 PM
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.

"Kelvin Beaton" <kelvin at mccsa dot com> wrote in message 
news:upGnTzDiHHA.4844@TK2MSFTNGP02.phx.gbl...
> thanks I'll take a look...
>
> Kelvin
>
> "Mattias Jonsson" <news@ett-remove-idesystem.se> wrote in message 
> news:u25XyuDiHHA.4872@TK2MSFTNGP03.phx.gbl...
>> 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:
>>> 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
>
> 


0
BruceM
4/27/2007 11:10:06 AM
Reply:

Similar Artilces:

how can i access CRM from ouside LAN
Hi All, if i have a implementation of MS CRM 3 in my office. All users of CRM can access it from within the LAN through windows authentication. How can they access it from their home or some remote position. i dont want to use offline CRM/outlook functionality. Can i access MS CRM 3 online from anywhere on internet, something like assignin it some IP. Please explain. You can serve it out just as you would any other website in IIS. Make sure you use an SSL certificate so your CRM data is protected behind https. If your CRM server has a fixed external IP address or is reachable via a URL, y...

Links is in gray color (deactivate). How can I activate?
In Edit Menu, I want to change the Links source but can do it as it is grayed. Help please. Thans Click the edit meun and hold the "shift" key together. yclhk "Rechie" wrote: > In Edit Menu, I want to change the Links source but can do it as it is grayed. > Help please. > Thans > Hi, Links Menu is still with gray color which means is not activated. "yclhk" wrote: > Click the edit meun and hold the "shift" key together. > > yclhk > > "Rechie" wrote: > > > In Edit Menu,...

Validating Data on a Form with a subForm
I have a form with subforms with data validation in the main Form's Before_Update Event. The problem that I am encountering is when the user has not updated all required data but enters the subform the Form Before_Update Event fires and the validation will fail. Is there a way to know what the next object has been selected might be or how I would not perform the Form's validation when the subform has been selected? Any help will be appreciated!!! No. Access saves the main form's record at the point when you move from the main form into the subform. Any record that fails v...

Can't delete additional "Personel Folders Outlook 2003
I followed a backup procedure form MS Office site that said to check the resulting PST file by opening in Outlook. I did it twice. Well, it created two other "Personal Folder" at the bottom of the Mail/All Mail Folders pane that I cannot delete. I moved or deleted the two backed up PST files so there is no data for the folders to relate to. How can I get rid of these erroneous folders? Gary Create a new mail profile - yours is now corrupt. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here)...

Can I convert an Outlook 2000 PST to Outlook 2003
We have upgraded from Outlook 2000 to Outlook 2003. Is there a fast way to convert my old PST files to Outlook 2003 so that they are not limited to 2 GB size? I don't want to have to go through all of the folders on 30 computers and drag one folder at a time from the old file to the new file. Thanks, Joe I have had pretty good luck importing. Of course, when you import, you = will no longer have access to any custom forms, views, or Rules Wizard = rules.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.h...

Code for my Monthly calendar..!!!!
I have a small monthly calendar that has some code for the days, but seems to be a problem, it doesnt sometimes know how many days in the month...It is a small program. If you want to look at it Ill be glad to send it.. Btw its excel 2000..Bill not sure what your trying to accomplish but you dont need to use code to find out the number of days in the month use this formula to find the days in the current month =day(eomonth(now(),0)) this will give you the number of days in a month =day(eomonth(date(2003,12,1),0)) if you want a copy of a xl spread sheet that will create a calander...

Discover CVV Code
Please add the ability to capture and pass the CVV code to the merchant processor for Discover. Discover charges retailers a higher rate without it. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowse...

Code Union Query
I have a Union Query looking like this: SELECT ALL * FROM[invoicetrading]; UNION ALL SELECT * FROM[invoicesnontrading]; I would like that the query is sorted by accountnumbers. I.e. I have accountnumbers like 43001000-6000 - and I would like that the query is sorting them on the last four digits (Something like: Sort *####). How can I alter my code so that it is done automatically? Thanks Klaus Can we assume that both your tables have the same number of fields, of the same type, in the same order, and that one of those fields is called AccountNumber? Try something like this: SELE...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

enter data to different tables through one form
i have 5 tables. all of them has pers_no as primary key. they have one to one relation ship through the primary key amongest them. i want to create one form having a text box pers_no where i will enter the data and i want the data to get entered in each table. so how i am not able to do that. i request all you guys to help me. thank you. Sorry for the bad news, but that's not the right way to use Access. You cannot write to 5 tables from one form like that. If you are subclassing, you could create a form for entering the main form info, and then 5 subforms (probably on tab pages) f...

How can comments be inserted into a document ?
Can comments be inserted into a documents done in Publisher? If so, how? No..not automatically. You should be using Word. There is always the callouts in Autoshapes. -- Mary Sauer http://msauer.mvps.org/ "Susan" <Susan@discussions.microsoft.com> wrote in message news:E9195C75-EB38-45A2-ABA4-879C86988A9C@microsoft.com... > Can comments be inserted into a documents done in Publisher? If so, how? ...

SumProduct with date and time
I have finally found that with my formulas the calulations are based on the time. Can someone help me find the best way to correct this without using macros to strip the time out of the data. Bob 09/08/2007 01:05 13/08/2007 00:00 Bob Bill 10/08/2007 23:39 Bill Kyle 11/08/2007 07:36 Kyle Bob 16/07/2007 20:39 Bill 27/07/2007 19:59 Kyle 18/07/2007 16:51 Bob 14/07/2007 16:31 Bill 21/06/2007 16:46 Kyle 30/06/2007 16:55 Bob 12/06/2007 01:05 Bill 13/06/2007 23:39 1 2 0 1 1 Kyle 14/06/2007 07:36 1 1 1 1 1 Bob 12/01/2007 20:39 1 1 2 0 1 Bill 13/08/2006 19:59 Kyle 14/05/1999 16:51 These are t...

Can't connect to links
We have 3 computers all running XP Pro connected through a Linksys router. Problem is when I click on a link in an email on my desktop (Outlook 2003) I am directed to www.comcast.net instead of the site. I have tried several different links. I am able to connect to the sites through my Comcast web based mail, my wife's computer (Outlook 2003) and my laptop (Outlook 2007). I did a detect and repair with the Office 2003 CD and the problem continues. Any ideas please? Thanks, ...

Can't recieve and keeps asking for username and password?
I have been using windows live mail for the past 3 weeks and before that i was using regular windows mail. All of a sudden it started asking me for my password and username once i add another account. I have 3 accounts on there. Not only does it keep asking for that I can't send messages and don't think i can recieve them. Why is it doing this. I know my password and username but it won't take. I don't know what the deal is. It is the logon box that i never saw before. Can someone help me -- Sharae Schneider Is it an email account prompt or a prompt to sign o...

Can't add new entries.
This is a multi-part message in MIME format. ------=_NextPart_000_002B_01C47623.EDA716E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable All of a sudden my Microsoft office won't let me make any new entries or = change any existing ones. I just get a message "Transaction can not be = entered". What can I do to let me make new entries? ------=_NextPart_000_002B_01C47623.EDA716E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML...

can I network this?
I have Money 2000 is it possible to network the information to my laptop? and how? Yahoo I did it all by my self!!!!:D >-----Original Message----- >I have Money 2000 is it possible to network the >information to my laptop? and how? >. > Be very careful to avoid having the file open from both machines simultaneously even for MoneyExpress. Money provides no protection from corruption in this case. "becky" <Arvila1@tds.net> wrote in message news:058401c3570c$bb54d970$a501280a@phx.gbl... > Yahoo I did it all by my self!!!!:D > >-----Original Message-...

Validation code for macro to run
Hi...the code below works fine, but how do I change it so that it doesn't look for a blank cell (""), but looks for specific text in a cell? There is an If formula in cell C269 so that if 2 figures balance then this cell displays "TRUE", otherwise it displays "FALSE". The code mustn't run if Cell C269 displays "FALSE". If [C269] = "" Then MsgBox "The figures don't balance!!" Exit Sub End If Range("E1:E48").Select Selection.Copy End Sub Thanks. Rob Rob, try this, If [C269] =...

Code generation with types from other schemas
I want to be able to define types in one schema, and then use them in other schemas. So far so good. Now I also want to be able to generate classes from these schemas and use them independantly, and in their own namespaces. For example, schema1.xsd ==> schema1.cs schema2.xsd ==> schema2.cs where there are types from schema1 used in schema2. When I use xsd.exe to generate schema2.cs and an <xs:import> element, it works, but also regenerates the types from the schema1.cs. I guess the question is, is there a simple way to generate code for what I'm trying to do, or do I n...

RMS should show what bar code it is going to print
Okk when a item is made in RMS and the customer makes his own barcode and selects a barcode format to print it should show at the bottom of the screen what barcode is being created(as in the case of UPC, EAN it has a check Digit) That way the customer can put in the check digit in the number so it will scann after printing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the...

How can i look into a user's mailbox?
Hello, I'm a Exchange Full Administrator in a single domain testing environment. I've created a user account, mailbox-enabled it, and sent a mail. How can i see the contents of this user's mailbox, without actually logging on with his credentials? Can I look into the user's mailbox with System Manager? Thanks in advance, Mihai http://support.microsoft.com/kb/821897 -- Neil Hobson Exchange MVP http://www.msexchange.org/Neil_Hobson/ "Mihai_Admin" <mihaihudea@gmail.com> wrote in message news:1172010845.355156.229850@q2g2000cwa.googlegroups.com... > Hell...

Can I Save My Relationship
What if you could recapture your ex lovers mind, heart and soul?...Wipe the slate clean? Turn back time? Even if you feel right now that your situation is too far gone=85too screwed up =85or just plain too darn complicated? You already know how hard it is to just even wake up and roll out of bed in the morning. You leave your radio off on your way to work because every song is a painful reminder of him. You can't even bear to eat at the same restaurants you took her to. And if that isn't bad enough, you have to cope with the loss of friends and family that are on their side. Did you...

Java vs. C++ (Date class)
I'm porting some Java code into C++. Which class I should use instead Date class? Note that Date support miliseconds. Also, which class I should use instead of Vector class? std::vector? "Petar Popara" <my.fake@mail.net> wrote in message news:uG6VGpUfFHA.2424@TK2MSFTNGP09.phx.gbl... > > I'm porting some Java code into C++. Which class I should use instead Date > class? Note that Date support miliseconds. Oh dear. Both the Java and the C++ date classes do assorted odd things with time zones and "daylight saving"; neither is documented fully or ...

how to create a calendar to select a date in access 2007
I want to select a date from a pop up calendar on the form. Access 2007 automatically adds a date picker to a Date/Time field. -- Lynn Trapp MCP, MOS, MCAS "keithteri" wrote: > I want to select a date from a pop up calendar on the form. ...

Creation date
Hi, I would like a way to insert the creation date of worksheet, not the creation date of the template. -- Thank you in Advance Merci a l'Avance Martin Hi you'll need VBA for this. Find below a UDF for this task: Public Function Get_Creation_date() As Date Get_Creation_date = ActiveWorkbook.BuiltinDocumentProperties("Creation Date") End Function Enter the following in your target cell: =Get_Creation_date() and format the result as date -- Regards Frank Kabel Frankfurt, Germany Martin Racette wrote: > Hi, > > I would like a way to insert the creation d...

Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et
Hi, If the date was input as mm/dd/yyyy (12/31/2004), can I convert the format to "yyyy-mm-dd"(2004-12-31) or "mmm-yy"(Dec-2004)? i tried to change date format from "Format Cells-- Number tab--Category--choose "date" or "custom" but just failed. While Strangely, If i imput the data as "2004-12-31", i can change it to whatever date format i like. Appreicate your advice at earliest convenience. Thanks -- wintersunshine Hi sounds like your imported dates are not recognised as 'date' values but are stored as 'Text&...