Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL query in a module (Office 97 SR2)

    I have the following 3 line SQL query which I need to have as a VBA module. The field CODE has records such as NWM01 (always 2 numbers on the end). I'm trying to update a new record with the next available number i.e when a text box on the form is selected, the module checks the code and gets the next number i.e. NWM02 and drops it in the required field. The query below simply gets the last 2 numbers and adds a 1 to them but only where the code matches the code held in CatTrim.

    SELECT Max(Right([code],2)) AS Total, [total]+1 AS TotalNew
    FROM tblShortlistedEntries
    WHERE (((Left([code],(Len([code])-2)))="NWM"));

    So far I have come up with the following code, but it returns "Too few parameters. Expected 1."
    Set rec = CurrentDb().OpenRecordset("SELECT Max(Right([tblshortlistedentries].[code],2)) AS Total, [total]+1 AS TotalNew " & _
    "FROM [tblshortlistedentries]" & _
    "WHERE Left([tblshortlistedentries].[Code],(len([tblshortlistedentries].[Code])-2)) =" & CatTrim)


    Any help would be much appreciated, as I think it's only something like a missing bracket, and it's driving me mad.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL query in a module (Office 97 SR2)

    Set rec = CurrentDb().OpenRecordset("SELECT Max(Right(code,2)) AS Total, total+1 AS TotalNew " & _
    "FROM tblshortlistedentries" & _
    "WHERE Left(tblshortlistedentries.Code,(len(Code)-2)) =" & CatTrim)



    I've shortened it to the above code, and now I get "Syntax Error in JOIN Operation". There's no join though???? Any help would be much appreciated.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL query in a module (Office 97 SR2)

    Let's make it a little clearer:

    <pre>Dim strSQL as String

    strSQL = "SELECT Max(Right(code,2)) AS Total, _
    & " total+1 AS TotalNew " _
    & "FROM tblshortlistedentries" _
    & "WHERE tblshortlistedentries.Code Like '" _
    & CatTrim & "*'"

    Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)</pre>

    Doing it this way, you can break on the Set rec line and examine the contents of the strSQL variable to make sure it's giving you a valid SQL string. Using the Like operator means you don't have to do any calculating in the Where clause.

    You appear to be trying to open a recordset and calculate the next highest number for a particular variable value (cattrim). Since the variable has to be a string from the way you described it, you need single quotes before and after the concatenated value, as you can see from looking at the way I've broken it out.

    I recommend, though, that you dim a database object variable and use that rather than CurrentDb in the OpenRecordset statement. It's better programming and it creates a more robust recordset object. Just remember to destroy both object variables at the end of the routine.

    As to the error message you got, try it with the Like operator and see if it goes away. I've seen odd error messages when using a calculation in the Where clause and not fully referencing all the involved fields (i.e., len(Code) instead of len(tblshortlistedentries.Code) ).
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL query in a module (Office 97 SR2)

    Thanks for your reply Charlotte, it was a great help.
    Turns out it was returning errors because I'd missed the quotes out!

    I haven't had time to try your recommendation yet, but I will do at some point, also by destroying the variables, did you just mean setting them to null or "" or is there a command for doing it properly?

    I'm now trying to get around the bugs in my function, such as if the number is <10 it drops the 0, so I have to add it back in. Not difficult, I know, but a bug is a bug.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL query in a module (Office 97 SR2)

    With object variables like recordsets and databases, you destroy them by setting them equal to Nothing, not null and not "". Nothing has a particular meaning and is used to destroy object variables, so you would do something like this:

    Set rec = Nothing
    Set dbs = Nothing

    As for your "bug", numeric values don't include leading zeros because numbers don't. You can format a number to *display* them, but any "number" with a stored leading zero is actually a text string. Anything else is just for display purposes and is not the actual value.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL query in a module (Office 97 SR2)

    Thanks for the info on destroying variables.
    As for the zeros, I knew about it dropping the leading 0, but if I didn't put it back in when I eventually had my new code, the Max(Right(code,2) part wouldn't work due to there only being one character.

    My VBA is getting there slowly, but I should really go on a course of some sort. Currently I'm using the Access 97 Bible, Beginning Access 97 VBA programming, the Access help files and helpful people like yourself.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL query in a module (Office 97 SR2)

    If you need the leading zero to concatenate a 2 digit string, just test to see if the value of the number is less than 10. If it is, concatenate a "0" and then the number. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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