Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Dim where as variant in onclick

    I am creating a select query with the querydef procedure in Access 07 VBA.

    I am having trouble adapting a text field to return only records that do not equal certain text, that are blank.
    This field in the table is blank or it is "REG HIGH" - that's it
    what is the best way to write this where clause to give me the blanks?

    where = where & " AND [Unapplied] <> '" + "REG HIGH"

    Thank you in advance for your help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If by "blank" you mean Null, the syntax is:
    Code:
    where = where & " AND ([Unapplied] Is Not Null AND [Unapplied] <> 'REG HIGH')"
    If by "blank" you mean zero-length string (""), the syntax is:
    Code:
    where = where & " AND ([Unapplied] <> '' AND [Unapplied] <> 'REG HIGH')"
    or:
    Code:
    where = where & " AND ([Unapplied] Not In ( '', 'REG HIGH'))"
    Check the table definition (properties Required and Allow Zero Length of the column Unapplied) to know which one to use.

    Note: In <> '' and Not In ( '', '' are 2 single quote (') characters, not 1 double quotes (") character.
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Required is No and Allow Zero Length is Yes

    So I tried the zero length sample you gave and query returns all results

    I tried it as this and get all results:
    Code:
    where = where & " AND ([Unapplied] <> '' )"
    I need it to return the records that do not contain REG HIGH

  4. #4
    Join Date
    Jun 2010
    Posts
    186
    all of these samples are returning all results

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where unapplied <> 'REG HIGH'
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2010
    Posts
    186
    After trying every sample, and adjustments of my own to these samples - the query returns all results.
    I'm wondering if it's just not triggering at all with this code:

    Code:
    Private Sub cmdWCIG_Click()
    Dim DB As Database
    Dim qdef As QueryDef
    Dim where As Variant
    
    Set DB = CurrentDb()
    
    On Error Resume Next
    DB.QueryDefs.Delete ("GoalsA")
    On Error GoTo 0
    
    where = where & " AND ([Unapplied] Is Not Null AND [Unapplied] <> 'REG HIGH')"
    
    
    
    Set qdef = DB.CreateQueryDef("GoalsA", "Select * from qryMainReport1")
    DoCmd.OpenQuery "qryMainReport1"
    
    End Sub
    qryMainReport1 contains all my fields
    GoalsA is the temporary query to pick up the variants, but it doesn't appear to be pulling anything? When I open it - there are no fields

    any ideas?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    at what point do you limit the rows returned using a where clause?
    as far as I can see you don't use the variable 'where' at all.

    does qryMainReport1 return rows?
    After trying every sample, and adjustments of my own to these samples - the query returns all results.
    GoalsA is the temporary query to pick up the variants, but it doesn't appear to be pulling anything? When I open it - there are no fields
    are mutually incompatable, which is it either its returning
    all rows
    OR
    no rows
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2010
    Posts
    186
    In my previous experience with querydef - I create the query that will run my report, transfer to spreadsheet, open - whatever the case is
    that is my qryMainReport1
    then I take qryMainReport1 into a temporary query - GoalsA

    so my querydef code exists of 2 queries - the non-parametered qryMainReport1 and GoalsA holds the fields I am using as variant/criteria

    To answer your question - both qryMainReport1 and GoalsA are returning all rows....there is no criteria coming in from the code

  9. #9
    Join Date
    Jun 2010
    Posts
    186
    I am using this example as my starting point:

    Code:
    Private Sub NEexport_Click()
    Dim DB As DAO.Database
    Dim QD As QueryDef
    Dim where As Variant
    
    Set DB = CurrentDb()
    
    On Error Resume Next
    DB.QueryDefs.Delete ("Customers_History")
    On Error GoTo 0
    
    
    where = where & " AND [NENO] LIKE '" & Me![txtNENO] & "***'"
    
    Set QD = DB.CreateQueryDef("Customers_History", "Select * from qryPODEFS " & (" where " & Mid(where, 5) & ";"))
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Customers_History", "H:\Collections\TEMPLATE NENO", 0
    
    End Sub
    in this example - Customers_History record source is qryPODEFS and the only field it shows below is NENO.......the criteria changes there depending on what is entered in the textbox txtNENO in this where variable

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Foskbou View Post
    Required is No and Allow Zero Length is Yes

    So I tried the zero length sample you gave and query returns all results
    1. If Required is no, it means that Null are allowed, so:
    Code:
    where = where & " AND ([Unapplied] Is Not Null AND [Unapplied] <> 'REG HIGH')"
    Quote Originally Posted by Foskbou View Post
    I tried it as this and get all results:
    Code:
    where = where & " AND ([Unapplied] <> '' )"
    I need it to return the records that do not contain REG HIGH
    2. My second example was:
    Code:
    where = where & " AND ([Unapplied] <> '' AND [Unapplied] <> 'REG HIGH')"
    Have a nice day!

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Foskbou View Post
    In my previous experience with querydef - I create the query that will run my report, transfer to spreadsheet, open - whatever the case is
    that is my qryMainReport1
    then I take qryMainReport1 into a temporary query - GoalsA

    so my querydef code exists of 2 queries - the non-parametered qryMainReport1 and GoalsA holds the fields I am using as variant/criteria

    To answer your question - both qryMainReport1 and GoalsA are returning all rows....there is no criteria coming in from the code

    based on the code you have posted
    Code:
    'you define a variable that holds a where clause
    'im not to certain if you need to use the word 'where' when defiining a querydef, but even if you do its irrelevant, because at no point do you use this varaibel
    where = where & " AND ([Unapplied] Is Not Null AND [Unapplied] <> 'REG HIGH')"
    
    
    'you then create a querydef which is exactly the same as qryMainReport1
    Set qdef = DB.CreateQueryDef("GoalsA", "Select * from qryMainReport1")
    
    'you then use qryMainReport1
    DoCmd.OpenQuery "qryMainReport1"
    so no matter how helpfull Sindho is being, it doens't matter as you don't use the varaible 'where'
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2010
    Posts
    186
    Thank you both for your feedback on this!
    I got it to work.......

    there was error in my code where it wasn't looping around completely, so I fixed that

    What ends up working for unapplied is:
    where = where & " AND IsNull([Unapplied])"

    kudos!

Posting Permissions

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