Results 1 to 6 of 6

Thread: Wits end...

  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Angry Unanswered: Wits end...

    Hi, I would be eternally indebted to anyone who could help me with this problem...

    I creating a form to display product details...

    I have set up a number of controls at the top of the form, which are then used in a query, for the purposes of filtering the records displayed by a subform beside it - the ides being that the user can search for products based on specific requirments then select form the products satisfying those criteria in the subform ... it makes sense to me!?

    After some fun (or not?!) determining the best route to achieve this - I am going to write the query in code, it's just more versatile - I have now hit a major, and incredibly annoying hurdle...

    What is the value of a Combobox before any value is selected, I tried testing for "", and got no response, then I tried testing for NULL and got a message about improper use of NULL - what should I do. I need to use the test for whether a value has been entered to determine whether to include a where clause for that control...

    Hope this makes a bit of sense...if not say so and I'll try to explain a bit better.

    Thanks

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    The natural default value of controls is Null. To test for data, unless you specify ="" or some other default value in the control's properties. Use If IsNull(Me!ComboBox) Then . . . . OR If Nz(Me!ComboBox,"") = "" Then . . . . (which is the safest) to test for a value.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What I've found that works best for me (and covers both Null and empty strings) is a test like this:

    If SomeCombobox.Value & "" <> "" then ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jul 2004
    Posts
    34

    Unhappy

    Ok, so...

    I sorted out the problem I was having (I used the Nz function - many thanks) but now for some reason access won't accept my SQL statement.
    First thought something must be wrong so I created a version of the query in the builder, and then compared it to the code my function generated in vba - they were identical (bar a few brackets). So I tried inserting the SQL string form the query builder into my function, and got a 'Type Mismatch' message - I don't get it. It can't be an SQL dialect thing, since vba passes the query back to jet, hence I must be misusing the QueryDef.

    I am using :

    Dim dbs as object (I don't seem to be able to use Database)
    Dim adf as object (again queryDef isn't available)

    (Note here I'm using 2000, but my references are for 2003, I think the syntax must have changed?!)

    Set dbs = Currentdb

    -Create strSQL

    Set qdf = CreateQueryDef("test", strSQL)

    Any ideas?!

  5. #5
    Join Date
    Jul 2004
    Posts
    34

    Red face

    I've worked out the problem...

    I'm using a different computer (not my own) and the DAO object Library wasn't loaded - d'oh - so it's no wonder I was having such problems!

  6. #6
    Join Date
    Jul 2004
    Posts
    125
    . . . and I was just about to mention the same thing. If you have any issues with DAO, queries, recordsets, etc. just email me. Good luck

Posting Permissions

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