NZ Function Problem

[stepdown]

Hi, I am trying to use the NZ function but I just can't seem to get it to work when it looks up two different fields, here is what I have at the moment...

Criteria for Date:
Like NZ("[Forms]![TimeEntry]![DateTop]","[Forms]![TimeQuery]![DateTop]")

As well as...

Criteria for Staff ID:
Like NZ("[Forms]![TimeEntry]![StaffTop]","[Forms]![TimeQuery]![StaffTop]")

The reason behind this is I want to run the same query from both the TimeEntry and TimeQuery forms, and as you can see I need to make sure that both DateTop and StaffTop are used as criteria. I found people having similar problems here but it didn't seem to be resolved, and I have tried the different versions there. Is there a way that I can get this to work?

As always any help will be greatly appreciated

 

[neileg]

Nz() won't take two parameters like that, so I'm not surprised it doesn't work. The syntax is Nz(data which may be null, value to substitute if data is null).

 

[stepdown]

I thought as the fields may or may not be null it should work, looks like I was barking up the wrong tree

Sorry about that, could you give me some pointers as to how to achieve this effect another way?

 

[neileg]

I'm not crystal clear on what you are doing. I can see that you are trying to use two criteria on the same field but one or either may be null. What do you want to happen if they are null?

 

[stepdown]

Well, TimeEntry has a query on it, and the form to view this query through is TimeQuery. What I want to do is run the same query again from TimeQuery, an example...

I enter Staff ID - BF and Date - 01/01/06 in the TimeEntry form. The results open up in TimeQuery. Now I want to find Staff ID - MJ and Date - 01/02/06 from a similar looking combo box and text box on the TimeQuery form.

At the moment the query only allows the search to be performed from TimeEntry. As the form TimeQuery is linked to one query then you can't create a new query for that purpose, it will mean that you have additional queries for each form, and as you search on each new query form you end up in a bit of a cycle as TimeQuery's results show up in TimeQuery1 who shows up in TimeQuery3 etc. as the values cannot be fed into the original TimeQuery form as it is linked to the original TimeQuery query.

If the TimeEntry field has nothing in it, I want the query to use the values in the TimeQuery field, and I thought this would be the way to go about it. If this can be achieved more elegantly another way, I am very open to suggestions.

If this doesn't make sense please say, thanks again for your help

 

[neileg]

Do you want to retain the data for BF and add the data for MJ to your list, and then perhaps add another staff's data? If so, you might find it easier to use a temporary table and run an append query to add the records to this. Temporary tables are not actually temporary, but the data is. You would delete the contants of the table after you have finished.

 

[stepdown]

Erm, I think I will illustrate the problem with pictures when I get home and have a bit more spare time and photoshop, I'm finding it really difficult to explain my problem properly.

Thanks