ehhhh Getting multiple criteria.. I should know this by now I know

[Ruegen]

DoCmd.OpenForm "frmSchools", , , "SchoolTypeID = 2 or 9 or 10" And Not IsNull(CallBackDate) And TourOrganiserID = Forms!frmSwitchStaff!txtTele

trying to get the form to open all SchoolyType with id's 2, or 9 or 10


DoCmd.OpenForm "frmSchools", , , "SchoolTypeID =" & 2 & 9 & 10" & And Not IsNull(CallBackDate)" & And TourOrganiserID = Forms!frmSwitchStaff!txtTele"

or something like this?

[June7]

"(SchoolTypeID = 2 Or SchoolTypeID = 9 Or SchoolTypeID = 10) And Not IsNull(CallBackDate) And TourOrganiserID=" & Forms!frmSwitchStaff!txtTele

[Ruegen]

Thanking you!

[Ruegen]

"(SchoolTypeID = 2 Or SchoolTypeID = 9 Or SchoolTypeID = 10) And Not IsNull(CallBackDate) And TourOrganiserID=" & Forms!frmSwitchStaff!txtTele

When you have something like above and you want to add a field with a date criteria that is not on the form (a set date) why would I get a datatype mismatch?

"(SchoolTypeID = 2 Or SchoolTypeID = 9 Or SchoolTypeID = 10) And Not IsNull(CallBackDate) And TourOrganiserID=" & Forms!frmSwitchStaff!txtTele

but now I want to add CatalogueLabelPrinted (date field) within a specific date 1/5/2013

I know that dates have to be within hash like so #1/5/2013#

I guess that CatalogueLabelPrinted requires "CatalogueLabelPrinted >"

however joining it to the above criteria so that the form opens with that isn't working.

I'm trying to wrap my head around concatenation and using and but I don't quite get it on how to understand to do it freely whenever.

[June7]

CatalogueLabelPrinted is a date/time type field? It is in the form RecordSource?

"(SchoolTypeID = 2 Or SchoolTypeID = 9 Or SchoolTypeID = 10) And Not IsNull(CallBackDate) AND CatalogueLabelPrinted>#1/5/2013# And TourOrganiserID=" & Forms!frmSwitchStaff!txtTele

[Ruegen]

DoCmd.OpenForm "frmSchoolsCatalogues", , , "(SchoolTypeID = 2 Or SchoolTypeID = 9 Or SchoolTypeID = 10) And Not IsNull(CallBackDate) AND CatalogueLabelPrinted> #1/5/2013# And TourOrganiserID=" & Forms!frmSwitchStaff!txtTele

Works!
questions

why when doing this do you never use say

"(SchoolTypeID = 2 Or SchoolTypeID = 9 Or SchoolTypeID = 10) And Not IsNull(CallBackDate) & "AND CatalogueLabelPrinted>" & #1/5/2013# & "AND TourOrganiserID=" & Forms!frmSwitchStaff!txtTele

I'm not clear as to where you should and shouldn't use "

[June7]

You have an unnecessary & " in the expression. The date can be concatenated but only useful if it is a reference to user input, like with TourOrganiserID.

All literal text (non dynamic, non variable parts) go within quote marks. Concatenate variables.

[Ruegen]

You have an unnecessary & " in the expression. The date can be concatenated but only useful if it is a reference to user input, like with TourOrganiserID.

All literal text (non dynamic, non variable parts) go within quote marks. Concatenate variables.

what happens if the date field is null? as in CatalogueLabelPrinted>#1/5/2013#

could you just add CatalogueLabelPrinted>#1/5/2013# or null

?

[June7]

You want to return record if the CatalogueLabelPrinted field has no data?

Try:

CatalogueLabelPrinted>#1/5/2013# Or CatalogueLabelPrinted Is Null

or

Nz(CatalogLabelPrinted,#12/31/2099#)>#1/5/2013#

[Ruegen]

Nz(CatalogueLabelPrinted,#12/31/1888#)<=#1/5/2013#

Thanks I added that one and it works like a charm. Not sure if it is picking up the nulls yet, hoping it does.

[Ruegen]

Seems to

Please nobody ask me why I used 1888. It's nothing significant to me. It was only the year I was born.