Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Syntax error in date in query expression! Access 2007

    I recently got a new computer and transfered my database from old computer and Access 2003 to the new one running Access 2007!

    Everything worked fine on the old computer and on the old version of Access!

    When I run following code:

    insertTimeString = "INSERT INTO TimeExecution (Broj,Liga,Vrijeme) VALUES (" & i & ",'" & ListaLiga![League] & "',#" & Now() & "#)"
    'MsgBox insertTimeString
    DoCmd.RunSQL insertTimeString


    I get the following error messsage:

    Run time error 3075
    Syntax error in date in query expression '#8.10.2008 13:48:04'


    When I use msgbox do display my query string it looks like this:

    INSERT INTO TimeExecution (Broj,Liga,Vrijeme) VALUES (1,'Albanian 1st league 1999/200',#8.10.2008 13:48:04#)

    which looks like properly formed query string to me!

    So, WHAT'S WRONG?
    Why is it working on my old Access, why is runSQL command ignoring properly closed date string and keeps omitting closing #?
    Has something changed in Access 2007 regarding using dates in queries?

    Thanks in advance!

  2. #2
    Join Date
    Apr 2007
    Posts
    108
    SOLVED!

    You can't have dots in dates!

    http://forums.devarticles.com/micros...plz-88160.html

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your date format is incorrect - slashes instead of periods methinks
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sniped!
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    108
    Thanks anyway georgev!

    It was made to measure how fast is that new computer by executing same queries (that have real life use - not invented) on both machines!

    Resulst:
    Old computer - total test time 9:14
    New computer - total test time 5:51

    Was it worth buying?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is that 5 minutes or 5 seconds?

    That single INSERT query takes 5 minutes/seconds?!
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2007
    Posts
    108
    It's 9 minutes and 14 seconds an and 5 minutes and 51 seconds respectively!

    INSERT QUERY is instantenous and only inserts timestamp when a single query cycle that calculates data on 170K records is completed.

    Results above represent time it takes to complete 50 cycles on the old and on the new machine!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there anyway you could make this set based as opposed to procedural?
    Explain a bit more what you're trying to achieve.
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2007
    Posts
    108
    I'm not trying to achieve anything in particular!

    I had a set of 170 000 records representing results of historic soccer matches, I simply wanted to calculate stats that describe each clubs performance before playing that particular match, for example after 7 games in English Premier League Aston Villa has 4 1 2 record and goal record 12:10!

    I wanted to create queries that calculate that! IT'S ACHIEVED! I'm just not very happy with the amount of time it takes to calculate that! The original question is answered, so I had no particular point to make apart from philosophical mumbling about wheter the speed of the new computer is adequate!

    Thanks for taking interest georgev!

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There's nothing wrong with striving for improvements

    But hey, if you're happy...













    Quote Originally Posted by Riorin
    I'm just not very happy with the amount of time it takes to calculate that!
    George
    Home | Blog

  11. #11
    Join Date
    Jun 2016
    Posts
    6
    [QUOTE=gvee;6361486]There's nothing wrong with striving for improvements

    But hey, if you're happy...



    Can anyone help me to filter my crystal report using one textbox for the MEM_CODES field and two datetimepicker for my SDATE field.
    I need to filter the report by entering member's codes and date from , date to using datetime picker.


    My current codes are :
    Dim fechadel As String = Format(DateTimePicker1.Value, "M/dd/yyyy H:mm:ss")
    Dim fechal As String = Format(DateTimePicker2.Value, "M/dd/yyyy H:mm:ss")

    report("SELECT * FROM DTR_REC WHERE SDATE >='#" & fechadel & "#'AND SDATE <= '#" & fechal & "#' ", "CrystalReport1")
    i've encounter this error: " Data type mismatch in criteria expression."

    SDATE is datetime datatype

    I hope you can help me guys with my errors... Thanks

Posting Permissions

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