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.
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.
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!
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?