dcsimg
Results 1 to 33 of 33

Thread: syntax error in number in query expression number and date in vb6

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    syntax error in number in query expression number and date in vb6

    Sorry to come back to you but I still have not resolved my concern DAO vB6. but the command line works in VB5 why I do not know ...... If a person has an idea here is an image and some of the code otherwise I leave you a link from the program file and the database data that does not work. Otherwise if you do not arrive in ADO do it in recent ADO data control.

    here is the code that works in VB5. stating that c is the date giving the error for VB6
    [CODE]
    DataHisto.DatabaseName = App.Path & "\ technique.mdb"
    DataHisto.RecordSource = "select * from histo where number =" & Val (LblNumCar) & "and date =" & Format (ListDateh) & ""
    DataHisto.Refresh
    [/ CODE]

    Name:  Capture.jpg
Views: 532
Size:  40.1 KB

    i also tried this:

    [CODE]
    Data1.RecordSource = "select * from histo where number =" & Val (LblNumber) & "and date = #" & Format (ListDateh) & "#"
    'or
    DataHisto.RecordSource = "select * from histo where date = #" & Format (ListDateh, "dd.mm.yyyy") & "#"
    'or
    DataHisto.RecordSource = "SELECT * FROM histo WHERE number =" _
    & Val (LblNumber) _
    & "AND date = #" _
    & Str $ (CDate (ListDateh)) _
    & "#"
    [/ CODE]



    always had the same error code 3075

    For those who want to have the form and test data here is the link:

    https://github.com/siadli/histo

    or ZIP.

    technique.zip


    "For those interested in the car repair management project for a full garage I can provide it"
    Thank you for your help and experience that I have unfortunately .....
    Attached Images Attached Images  
    Last edited by siadli; Aug 27th, 2018 at 03:00 PM. Reason: rajout d un lien du projet en question

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: syntax error in number in query expression number and date in vb6

    Your DATE needs to be in quotes (if MS SQL) - maybe #'s if ACCESS.

    Using VAL() to turn a string into a number to then concatenate that "value" into a string is just silly. It is a "number" in the query because it is NOT IN QUOTES in that final query string.

    If this is MS SQL get the query to work in a QUERY WINDOW in Management Studio and then code logic that makes that EXACT SAME string,

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    Lively Member
    Join Date
    Mar 2018
    Posts
    115

    Re: syntax error in number in query expression number and date in vb6

    put # around the date

    Code:
    Data1.RecordSource = "select * from histo where numéro=" & Val(LblNuméro) & " and date = #" & Format(ListeDate) & "#"

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: syntax error in number in query expression number and date in vb6

    and we're back again at using Parameters.....
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,773

    Re: syntax error in number in query expression number and date in vb6

    Zvoni - I wondered about that... can you use parameters on the RecordSource of a DataControl? Something tells me no, but I've never used the DataControl beyond some experimenting quite sometime ago, so I don't really know.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: syntax error in number in query expression number and date in vb6

    What does parameters have to do with this question?

    If the OP is unclear on quotes around dates and simple query syntax, then that lesson needs to be given. Session 2 would be parameters. If you skip lesson 1 you are NOT building a future coder at all!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,074

    Re: syntax error in number in query expression number and date in vb6

    Date and time literals in Jet SQL are delimited by "#" symbols but are formatted for the invariant locale (i.e. Earth Standard which is basically U.S. English).

    But you'd be far better off using a parameter query instead of cobbling together SQL text dynamically anyway. Then you don't have to format such values as text... just to have the database turn around and parse it back into live data to use it.

  8. #8

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Bonsoir et merci de votre aide mais ça me donne toujours la même erreur et c est bien sur la date qui donne l erreur
    Last edited by siadli; Aug 21st, 2018 at 04:18 PM.

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Bonsoir et merci de votre réponse si je comprends bien il faut 2 requêtes au lieu de regroupé les requête mais je pense que ce la requête de la date qui donne cette erreur même en délimitant par des symboles "#"

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: syntax error in number in query expression number and date in vb6

    Quote Originally Posted by techgnome View Post
    Zvoni - I wondered about that... can you use parameters on the RecordSource of a DataControl? Something tells me no, but I've never used the DataControl beyond some experimenting quite sometime ago, so I don't really know.

    -tg
    No idea, since i never used any of those controls in my life

    What does parameters have to do with this question?

    If the OP is unclear on quotes around dates and simple query syntax, then that lesson needs to be given. Session 2 would be parameters. If you skip lesson 1 you are NOT building a future coder at all!
    szlamany,

    in that case this begs the question, why in blazes he's even starting to code a UI, if he hasn't even the basics for a database down.
    Every DB-Design-Tool i know of has at least an option to fire some queries against your Database to check if the design-logic is sound.
    Nevermind reading up on the particulars of your chosen DBMS and its differences to other systems.

    He's using MS Access (see the file-extension "mdb" in his databasename?), and he's not using access to bulletproof his queries in Access itself?
    That's like:
    "Hey, i want to build a car from scratch all on my own, and have already started on doors and tires, but i have no idea how an engine works, or how the wiring is done"

    But i agree with you in one thing:
    He's doing step 2 before having done step 1
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Quote Originally Posted by szlamany View Post
    Your DATE needs to be in quotes (if MS SQL) - maybe #'s if ACCESS.

    Using VAL() to turn a string into a number to then concatenate that "value" into a string is just silly. It is a "number" in the query because it is NOT IN QUOTES in that final query string.

    If this is MS SQL get the query to work in a QUERY WINDOW in Management Studio and then code logic that makes that EXACT SAME string,
    Bonjour j utilise une connection Access

  12. #12

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    bonjour j ai pas compris votre reponse

  13. #13

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Bonjour l erreur vient de la recherche sur la date car en suppriment la numéro j ai la même erreur . avec ou sans #.
    Mais bravo a vous c est bien un logiciel pour voiture
    Last edited by siadli; Aug 21st, 2018 at 07:42 PM.

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: syntax error in number in query expression number and date in vb6

    English only here on this forum. Translate your post. Do it back to your language and back again to English might make your point clearer.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,506

    Re: syntax error in number in query expression number and date in vb6

    Utilises tu MS Access? Pardon, I mean, Sorry (Please use ENGLISH on this forum, even if you have to use a translator and copy-paste). It makes a difference which database you are using.

    Tell us which database, and then, show the exact NEW SQL statement that gives the 'erreur' (error).

    Sam

  16. #16
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,074

    Re: syntax error in number in query expression number and date in vb6

    You appear to be using the old intrinsic DAO Data Control. Like the newer ADO Data Control these have many limitations. For example they are limited to a crude RecordSource property. They cannot handle parameter queries at all.

    When you use these super-simplified database connectors you can only specify a table name, a stored querydef with no parameters, or a SQL statement with no parameters.

    Code:
        Data1.RecordSource = "SELECT * FROM histo WHERE numéro = " _
                           & LblNuméro.Caption _
                           & " AND [date] = #" _
                           & Str$(CDate(ListeDate.Text)) _
                           & "#"

    [date] needs brackets because date (or DATE, Date, etc.) is the Date() function in Jet SQL. I am assuming that your "date" is a field name in the table "histo" here.

    CDate() converts a String value to a Date type value using the current session locale.

    Str$() converts a Variant value to a String value, and always uses the Invariant Locale. It is obsolete for most purposes but can be useful here because of the accidental side effect that it uses the Invariant Locale. If a Date type value is passed to it the resulting String value will be formatted properly for Jet SQL: m/d/yyyy format.

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: syntax error in number in query expression number and date in vb6

    Quote Originally Posted by SamOscarBrown View Post
    Utilises tu MS Access? Pardon, I mean, Sorry (Please use ENGLISH on this forum, even if you have to use a translator and copy-paste). It makes a difference which database you are using.

    Tell us which database, and then, show the exact NEW SQL statement that gives the 'erreur' (error).

    Sam
    Sam,

    look at his first line in the first post. Take a look at his File-Extension.... "*.mdb"
    That's Pre-2007 Access
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  18. #18
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,506

    Re: syntax error in number in query expression number and date in vb6

    Ah..thanks Z....missed that one!

  19. #19
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,294

    Re: syntax error in number in query expression number and date in vb6

    If its access it may also be complaining about the date format. not sure it will work with full stops.

    My usual advice is to convert to #mmm/dd/yyyy# or #dd/mmm/yyyy# as these are unambigous (ie clearer) than the #dd/mm/yyyy# or #mm/dd/yyyy# which I have had problems in the past with windows, excel and access settings in the locals messing the dates up.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  20. #20
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: syntax error in number in query expression number and date in vb6

    Vince,

    the OP cross-posted the same question in the VB6-Forum, and i think dilettante was it, who showed a nifty way to avoid that conversion to locale-aware dateformat.
    I think it was something like "Str$(CDate(txtDate.Text))" or along those lines (too lazy to look up the thread now).
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  21. #21

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Quote Originally Posted by Zvoni View Post
    Vince,

    the OP cross-posted the same question in the VB6-Forum, and i think dilettante was it, who showed a nifty way to avoid that conversion to locale-aware dateformat.
    I think it was something like "Str$(CDate(txtDate.Text))" or along those lines (too lazy to look up the thread now).
    hello it's not a text box but a date list to retrieve in the database

  22. #22
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,058

    Re: syntax error in number in query expression number and date in vb6

    siadly,
    can you place your database here (with only the needed tables,and compacted, and zipped)
    and please explain clearly what you expect as a result
    do not put off till tomorrow what you can put off forever

  23. #23

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Quote Originally Posted by IkkeEnGij View Post
    siadly,
    can you place your database here (with only the needed tables,and compacted, and zipped)
    and please explain clearly what you expect as a result
    as agreed, I made you a zip. the concern that i have, know i had programmed a management software in vb5 that works well 'see the zip and putting it in vb6 after several modification of certain form works well jump that for form histo. the same function does not work anymore. I put the example that works in vb5 and the one in vb6 with the database. In fact I am looking for a visit of the customer thanks to the date with a drop-down list. I find all the dates of the customers but when I click on a date it puts the error 3075. I think it is the date that comes the error.
    Thank you for your intention and sorry for my poor English.
    Attached Files Attached Files

  24. #24
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,058

    Re: syntax error in number in query expression number and date in vb6

    does the following succeed ?
    Code:
    SELECT Numéro, Date FROM Histo WHERE Numéro=4 AND Date=#2017-06-29#
    do not put off till tomorrow what you can put off forever

  25. #25
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,747

    Re: syntax error in number in query expression number and date in vb6

    I suggested that same thing in post #2 - 22 posts ago.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  26. #26

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Quote Originally Posted by IkkeEnGij View Post
    does the following succeed ?
    Code:
    SELECT Numéro, Date FROM Histo WHERE Numéro=4 AND Date=#2017-06-29#
    Hello, I tried to add #. but the error is always the same ....

  27. #27

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Hello, I tried to add #. but the error is always the same .... To fully understand my problem try to take the project in zip thank you friends of your supporters

  28. #28
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: syntax error in number in query expression number and date in vb6

    I've looked at his source-code.
    What's wrong in this line (i see 2 errors)?
    Code:
    DataHisto.RecordSource = "select * from histo where date = # " & Format(ListeDateh, "dd.mm.yyyy") & " # "
    EDIT: Just saw it:
    That's 7 days he wasn't able to figure it out despite all hints and help he received
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  29. #29

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    Hello people from the forums

    After reading all your messages and testing by changing my locale by dd / MM / yyyy. for modified the date that becomes 28/08/2018 instead of 28.08.2018.

    My observations had a joy of thanks to all the participants.

    But I have a major concern that I can not understand is the first 12 days of each month "example: 01/03/2004 to 12/03/2004" the click on the list do not make any effect, then only on the other days of "13/05/2015 to 31/05/2015" it works. For what mystery reason.

    Did you have this problem?

    I have other questions to understand but try to find a solution for my 12th day that does not work .....

    And thank you for your precious help.

  30. #30
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,058

    Re: syntax error in number in query expression number and date in vb6

    there is no mysterious reason at all
    what is the date 05/06/2018 ?
    that depends on the part of the world you'r in
    it can mean the 5th day of June ---say in belgium
    or the 6th day of may ---say in the USA
    but: 2018/06/05 is the 5th day of june in any part of the world
    clearly you did not read/understand my post #24
    where i used the format YYYY-MM-DD for just that reason
    do not put off till tomorrow what you can put off forever

  31. #31
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: syntax error in number in query expression number and date in vb6

    Quote Originally Posted by siadli View Post
    After reading all your messages and testing by changing my locale by dd / MM / yyyy. for modified the date that becomes 28/08/2018 instead of 28.08.2018.
    Wrong!
    The Answer is in Post #16 in dilettante's last line of the post.
    And he gave you a solution without Formatting the Date around three corners
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  32. #32
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,058

    Re: syntax error in number in query expression number and date in vb6

    do not put off till tomorrow what you can put off forever

  33. #33

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    12

    Re: syntax error in number in query expression number and date in vb6

    I come to you to thank, without you I think that I will not have succeeded. I had to
    My problem is that my french region uses the "." for the date.
    Sorry for my mediocre English.

    my subject is this time resolved. Thank you very much
    Last edited by siadli; Aug 30th, 2018 at 02:16 PM.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width