Results 1 to 5 of 5

Thread: sql code

  1. #1
    Join Date
    Nov 2003
    New Zealand

    Unhappy Unanswered: sql code

    whats wrong with this line of code?

    Dim machine As String
    machine = (rstDetailLine.Fields("MachineID").Value)
    sqlMachine = "Select * from QuoteMachine Where ((QuoteMachine.Model_ID)) = machine "

    I get this error! no value given for one or maore parameters given.
    If i hard code the machine variable it works. I have printed out the machine value and it is what it should be but it still doesn't work what am i missing.

    I have tried these variations. and i get syntax error.

    sqlMachine = "Select * from QuoteMachine Where ((QuoteMachine.Model_ID)) = " &machine&" "

    sqlMachine = "Select * from QuoteMachine Where ((QuoteMachine.Model_ID)) = 'machine' "

    please explain to me what i am doing wrong.
    Nerdy Girl

  2. #2
    Join Date
    Dec 2003
    Convert machine value to string using CStr()
    Since machine is a variable, you have to add it to the rest of the SQL statement just like any other string
    Use NZ() to return "" (a blank string) so your query doesn't fail on null values from the table


    Dim machine As String

    machine = CStr(NZ(rstDetailLine.Fields("MachineID").Value,"" ))
    sqlMachine = "Select * from QuoteMachine Where QuoteMachine.Model_ID = """+machine+""""


    machine = CStr(NZ(rstDetailLine.Fields("MachineID").Value,"" ))
    sqlMachine = "Select * from QuoteMachine Where QuoteMachine.Model_ID = '"+machine+"'"

    1. always, when joining SQL statements convert to a string value
    2. for numbers you don't need the apostrophe (or double quotes)
    3. for dates use pound signs ## around the variable

    I don't have your original MDB to test so I hope I got the syntax right.

    Joe G
    Last edited by JoeG; 12-28-03 at 18:10.

  3. #3
    Join Date
    Nov 2003
    New Zealand
    BINGO. that was it. The syntax was perfect i used the first one and it ran straight away. I love the little comments. I'm a beginner and i need all the tips i can get.
    I'm still slightly confused about the apostrphes.....when to use " " or ' '

    the machineID in the table is a Text datatype and i declared 'machine' as string, why did i still have to do CSTR. Whats NZ? is this no value.

    Thanks that was exactly what i need......You know what i didn't even think to see what would happen if there was no value for the sql statement. Oh No!! thanks.
    Nerdy Girl

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    if machineID is already a string you shouldn't need cstr.

    nz(this, that) returns this if this is not null, else that

    dim strVar as string
    strVar = "that"
    "this and " & strVar concatenates to: this and that
    "this and '" & strVar & "'" concatenates to: this and "that"

    you need your string values "quoted" in your SQL, so ' is just a way to explain to VBA that you want a quote embedded in the string.

    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2003

    Here' are a couple of Microsoft articles on using apostrophes:;EN-US;147687

    On CStr() no need to use it. I usually keep my variables defined the same as the purpose they serve. For example, I wouldn't make a customer id a text field - I would define it as a number (unless I had no choice in the design).

    A good practice is to preface your variables with something to define what type of variable it is. Some common ones are:
    str = string
    var = variant
    int = integer
    lng = long

    Some people just use the first letter:

    For example, a long integer could be written as:
    lngMachineId or lMachineId

    You could look up standard naming for variables (called Hungarian notation) in most programming books or on the web.

    Naming your variables this way lets yourself and others know how it's defined - no guesswork.

    So if machine was written as strMachine, you would know right away not to bother with CStr(strMachine). Makes your life easier.

    Here's an article on NZ():

    One last tip:
    If you build your SQL statement in pieces, it might eliminate some of the problems with apostrophes and quotes since Access will add together the individual strings such as:

    strWhereClause="Where tblMachines.MachineId="
    strSQL=strWhereClause+" "+strMachineId

    Glad to help.

    Joe G

Posting Permissions

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