Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    49

    Unanswered: Simple SQL Query

    Now just need an easy way to find out the value of an sql query and im done

    DIM jmweb as string

    jmweb = Select number from tbl where name = $name, date = today


    How do I do this in Access so that jmweb would equal what ever is in the number box.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Try this:

    DIM jmweb as string
    jmweb = Nz(DLookUp("[number]", "[tbl]", "[name] = '" & $name & "' AND [date] = #" & Format(Now(), "Short Date") & "#"), "")


    .
    Last edited by CyberLynx; 11-24-05 at 01:38.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Nov 2005
    Posts
    49
    So....

    [number] = colum in db called number...
    [tbl] = I replaced this with tblaudits
    [name] = column in db called name
    $name = should be [cbname] (combobox name)
    [date] = column in db called date

    jmweb = Nz(DLookUp("[number]", "[tbl]", "[name] = '" & $name & "' AND [date] = #" & Format(Now(), "Short Date") & "#"), "")

    Is the above correct cuz I keep getting an error..

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by jmweb
    So....

    [number] = colum in db called number...
    [tbl] = I replaced this with tblaudits
    [name] = column in db called name
    $name = should be [cbname] (combobox name)
    [date] = column in db called date

    jmweb = Nz(DLookUp("[number]", "[tbl]", "[name] = '" & $name & "' AND [date] = #" & Format(Now(), "Short Date") & "#"), "")

    Is the above correct cuz I keep getting an error..
    Hi jmweb,
    Just one tip on field/object naming conventions. Try creating a bit more unique descriptive names and get away from Reserved Names such as DATE. That can cause a few problems down the road. If it's InvoiceDate then try InvDate or something of the sort. There are some links here about naming conventions and reserved names.

    have a nice one,
    BUD

  5. #5
    Join Date
    Nov 2005
    Posts
    49
    Thanks, what about my original question though??

  6. #6
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    What is the error?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    reminds me of an access oddity:

    Table gash:
    Code:
    idBlah   textmo           mydate
    1        December       11 December 2005
    2        November       12 November 2005
    and in the immediate window:
    Code:
    ? format$(now(), "Short Date")
    24/11/2005
    
    ? dlookup("textmo", "gash", "myDate = #12/11/2005#")
    December
    
    ? dlookup("textmo", "gash", "myDate = #" & format$("12/11/2005", "Short Date") & "#")
    December
    
    ? dlookup("textmo", "gash", "myDate = #" & format$(#12/11/2005#, "Short Date") & "#")
    November
    anyone with ROW date settings care to explain the logic in that 'November' lurking at the end? (i guess this doesn't happen with US date settings?)

    but back to your question:
    CyberLynx' code looks absolutely correct, Bud is right to be concerned about using reserved words for vars etc, and apart from that, maybe you dropped or gained a space or quote or something.

    post your finished line of code.

    izy
    currently using SS 2008R2

Posting Permissions

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