Date to Date query (1 Viewer)

[Pusher]

Forms!FrmPrikazIzvestaja!Combo32) Is Null is the thing i don't understand in the query. There should be all the lines in the table IZVESTAJ that have the Streets in the specific Teren between two dates. Then i delete this he gives me only 1300 - and there is 1900 lines in the IZVESTAJ table.
Note that this is the DB that you gave me so there is less lines in izvestaj ... now there is 3500 lines - every line is a part of a Teren .

 

[vbaInet]

So give me some criteria and tell me how many records you expect to see. And remember the Is Null criteria too.

 

[Pusher]

When I leave everything blank (in this new db I uploaded) – the Teren and MIN and MAX dates I get 1071 records and I have 3128 records in my IZVESTAJ table. That numbers should be equal.
The IS NULL is for all the Terens?
Maybe there is a problem with dates in IZVESTAJ table DATUM_IZVESTAJA?

When i delete the code

Between Nz([forms]![FrmPrikazIzvestaja]![txtStart];[forms]![FrmPrikazIzvestaja]![txtAltStart]) And Nz([forms]![FrmPrikazIzvestaja]![txtEnd];[forms]![FrmPrikazIzvestaja]![txtAltEnd])

I get all the 3128 records

 

[vbaInet]

Alright, two things:

1. It looks like I mistakenly put the criteria twice, i.e. the BETWEEN criteria in the OR row should not have been there. I don't know how that got there

2. When you are testing against a date field, ensure that the Format of the field is the same type as the criteria. You formatted your field as Long Date but you were using Short Date as the criteria. You can always create a separate Short Date column for your criteria and make it invisible and have your normal Long Date formatted date in a new column. Or you can convert your dates to an Integer type using CLng().

See attached the amended db.

 

[Pusher]

[FONT=&quot]Thanks, that did it I thought that it needed to be that way [/FONT]

 

[vbaInet]

You're welcome!

 

[Pusher]

How can i extract the value of NZ function so i can use it in a report that is from a query?

 

[CBrighton]

If you want to display the result of an NZ function applied to a field then you can use the field which has had it applied to as the control source of a textbox.

Alternatively if the field in the query has not had the NZ applied you can apply it in the control source of the textbox like this:

=NZ(FieldName,ReplacementForNull)

Basically the same way you would if you were using the function in a query, except the control source needs to start with = if you input more than just a field name.

 

[Pusher]

Can i enter this
=Nz([Forms]![FrmPrikazIzvestaja]![txtStart];[Forms]![FrmPrikazIzvestaja]![txtAltStart])
When i enter this i get #Error

 

[CBrighton]

Replace the ; with a ,

I've not referred to a form control on a report but I see no reason why it shouldn't work, as long as the form is open.

 

[Pusher]

When i replace , with ; i get a
the expression you entered contains invalid syntax
(
=Nz([Forms]![FrmPrikazIzvestaja]![txtStart],[Forms]![FrmPrikazIzvestaja]![txtAltStart])
This can't be entered... is there another way of entering a NZ value of two form values in a form?

 

[Pusher]

But this works
=Nz(([Forms]![FrmPrikazIzvestaja]![txtStart]);([Forms]![FrmPrikazIzvestaja]![txtAltStart]))

 

[vbaInet]

When i replace , with ; i get a
the expression you entered contains invalid syntax
(
=Nz([Forms]![FrmPrikazIzvestaja]![txtStart],[Forms]![FrmPrikazIzvestaja]![txtAltStart])
This can't be entered... is there another way of entering a NZ value of two form values in a form?

That's the literal character set by your country locale so it's fine.