Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    30

    Unanswered: Error when using Nz in select query

    I have a query that pulls from two different tables and I am having problems so I tested my tables individually in their own query just to see what data they were pulling.

    Table1 provides dates for an event in the query and Table2 provides start and end times for each of those dates. When I test Table1 in its own query it shows me the dates but does NOT exclude Null values for whatever reason. When I run Table2 in its own query it shows me the start and end times but it DOES exclude the Null values.

    I have no clue why this is happening because I have checked the data types in all of the fields used here and they are all set properly to long dates and medium times. Finally I combined the tables into one query and tried an Nz statement in the criteria for the start and end times to convert the Null values to 0 and when I try to run the query on a report I get this message: "An expression you entered is the wrong data type for one of the arguments". I would really appreciate some insight from anyone on this. I am stumped and google is running me around in circles. Thanks!
    Last edited by crosseydlobster; 03-15-12 at 22:55.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL sentence of the query as well as relevant information about the tables involved.
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    30

    all the relevant info i can think of

    Table1 includes the following fields: ID, firstname, lastname, email, event1date
    Table2 includes the following fields: event1dates, timestart, timeend
    Relationship: [Table1].[event1date] is related to [Table2].[event1dates] (both fields are long date formats) Table2 is meant to supply the start and end times for the event dates in Table1.

    The select Query I have created joins [Table1].[event1date] and [Table2].[event1dates]. However the query pulls from the following fields in each table: [Table1].[ID], [Table1].[event1date], [Table2].[timestart] and [Table2].[timend]... (both fields from Table2 are medium time formats)

    My goal is to create a query to include these fields mentioned above yet I need the query to include all records where any of these fields from either table are Null or blank. To make that happen I used the following criteria in the query: Nz([Table2]![timestart],0) and Nz([Table2]![timeend],0).

    I even tried the Nz statement in the criteria for [Table1].[event1date] but I still receive an error message. I tried it without it as well. Let me know what other info I can provide. Thanks so much!
    Last edited by crosseydlobster; 03-15-12 at 22:54.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Please copy and paste the actual SQL for the query where you use the Nz() functions. Something tells me you're not using it right.

    Sam

  5. #5
    Join Date
    Apr 2011
    Posts
    30

    copied and pasted

    Nz([TBL_customer]![photo1Register],0)
    Nz([TBL_photo1dates]![TimeStart],0)
    Nz([TBL_photo1dates]![TimeEnd],0)
    Last edited by crosseydlobster; 03-15-12 at 23:53.

  6. #6
    Join Date
    Apr 2011
    Posts
    30
    Here is my database... Take a look at FRM_customers. Search for each of the following customers: Jane Doe, John Doe, and Baby Doe.

    Each time you search go ahead and press the confirm button which will run a macro to open RPT_confirm, send it as an email attachment and then close the report.

    This only works for Baby Doe b/c Baby Doe is registered for all 4 events included in the report. In other words none of the fields are null for this record. However Jane and John Doe are not registered for each of those events meaning that it produces a Null for those fields, therefore we see the error message. The report is running from QRY_register and this is where I am attempting unsuccessfully to use the Nz statement to include those records with Null fields. I would love any insight! BTW this is still a work in progress. Thanks!
    Attached Files Attached Files

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by crosseydlobster View Post
    Nz([TBL_customer]![photo1Register],0)
    Nz([TBL_photo1dates]![TimeStart],0)
    Nz([TBL_photo1dates]![TimeEnd],0)
    Is photo1Register stored as a Numeric or a String field? Also, are TimeStart and TimeEnd stored as Date fields, or as String fields?

    I was hoping to take a look at the db after extracting it, but you are probably using a later version of the ZIP program than I have on my computer. I got an error meesage when I tried to open the ZIP file. Can you compact the db and upload it without ZIPping it?

    Sam

  8. #8
    Join Date
    Apr 2011
    Posts
    30
    Try this zip file instead. I used a different program...there should be no problems extracting the file. I also compacted it.

    Photo1register is stored as a Long Date
    TimeStart and TimeEnd are stored as a Medium Time

    Thanks again I really appreciate your thoughts on this.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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