Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    Unanswered: DATA TYPE MISMATCH IN CRITERIA EXPRESSION error msg

    Dim mystring As String
    Dim myDate As Date

    mystring = "02/01/2004 07:00:00"

    myDate = Format(mystring, "General Date")
    'myDate now looks fine "02/01/2004 07:00:00AM"


    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= '" & myDate & "'"

    Problem: I'm getting "DATA TYPE MISMATCH IN CRITERIA EXPRESSION"
    error message on the DoCmd.OpenReport statement -- which I don't understand since BOTH [start_date] and myDate are of "General Date" format.

    What am I doing wrong now?
    A2K - W2k
    Thanks & Regards
    Pete

  2. #2
    Join Date
    Feb 2004
    Location
    Kuwait
    Posts
    9
    try
    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= '" & #myDate# & "'"


    Good Luck

  3. #3
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    Try this:

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= " & DateValue(myDate)
    I won't byte... hard!

  4. #4
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    still not working

    Neither of the above 2 suggestions worked (see below).


    'DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= '" & #myDate# & "'" <- causes syntax error.


    'DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= " & DateValue(myDate) <- no error but it truncates the date/time to just
    the date

    (ie myDate = 02/15/2004 07:00:00AM
    DateValue(myDate) = 02/15/2004

    Also, I entered a date well beyond any [start_date] and all records were displayed in the report ... it's like the argument didn't xfer to the report.

    Any other ideas?
    A2K - W2k
    Thanks & Regards
    Pete

  5. #5
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    How about:

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= " & DateValue(myDate) & " " & TimeValue(myDate)
    I won't byte... hard!

  6. #6
    Join Date
    Feb 2004
    Posts
    199
    use this

    Dim mystring As String
    Dim myFormDate As Sting

    mystring = "02/01/2004 07:00:00"

    myFormDate = Format(mystring, "General Date")
    'myDate now looks fine "02/01/2004 07:00:00AM"


    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= #" & myFormDate & "#"
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  7. #7
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    36
    you should use sql date - this is much better practice as it puts the #'s in and flips the date formating if someone uses australian dating in an american dating db and visa versa ... using this will prob resolve your problem

  8. #8
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    36
    oh yea - and here is how u would code it :

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= " & SQLDate(myDate)

    also make sure that the field start_date is a date and not a string

  9. #9
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    i00, can you please give an example of 'sql date' in regards to this situation?
    I won't byte... hard!

  10. #10
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    It works!

    Thanks so much!
    A2K - W2k
    Thanks & Regards
    Pete

  11. #11
    Join Date
    Jan 2004
    Location
    Texas
    Posts
    37

    fyi

    DoCmd.OpenReport "report", acViewPreview, , "[start_date] >= #" & myFormDate & "#"

    that is ... hadn't seen the SQL threads ... my bad
    A2K - W2k
    Thanks & Regards
    Pete

  12. #12
    Join Date
    Feb 2004
    Posts
    199
    i00 is rigth about language setting problems, but I've never heared about SQLDate function , I suppose it is included in some extra tool that ataches to Access extra functions, what I did is to make some Function like SQLDate (I named it as GeFormtDateTime(mDate as Date) as String), and it better to use USA DateTime fomrat
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  13. #13
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    36
    no it is included in access... i don;t know if it prompts you ... i no that it isn;t in the object browser in office 2003 - but it still works

  14. #14
    Join Date
    Feb 2004
    Posts
    199
    ? it's no in MS Access2002/2003, can you locate a topic in MSDN
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  15. #15
    Join Date
    Feb 2004
    Posts
    199
    Aha!, I've found it SQLDate isn't MS Access Function!
    It's a VB Script function and you can use it in .ASP/.NET, not in Access VBA
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •