Invalid Use of Null?

[sanders720]

Why would this:

SELECT tblBOM.JobNo, tblBOM.SubAssy, tblBOM.PartNo, tblPartsListing.PartDescription, tblPartsListing.ManufacturedBy, tblPartsListing.Code, tblPartsListing.RevisionLevel, tblBOM.ReleasedBy, tblBOM.ReleasedDate, tblBOM.QTY FROM tblPartsListing INNER JOIN tblBOM ON tblPartsListing.PartNo = tblBOM.PartNo WHERE CDate(tblBOM.ReleasedDate) Between #9/1/2004# AND #9/30/2004#

produce an Invalid Use uf Null

 

[PHV]

You may try this:
WHERE CDate(Nz(tblBOM.ReleasedDate,0)) Between

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanders720]

Data Type Mismatch in Criteria Expression

 

[PHV]

My bad, forgot ReleaseDate is a string (!)
WHERE CDate(Nz(tblBOM.ReleasedDate,'0')) Between

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanders720]

'0' changed nothing on the string

"WHERE CDate(Nz(tblBOM.ReleasedDate,'0')) Between #" & Forms!frmReportsListing.txtStartDate & "# AND #" & Forms!frmReportsListing.txtEndDate & "#"

What do I do for date? Do I need to add "#" anywhere?

"WHERE tblECOList.ECODate Between #" & Forms!frmReportsListing.txtStartDate & "# AND #" & Forms!frmReportsListing.txtEndDate & "#"

Your replies are appreciated.

 

[PHV]

In a standard code module create this function:
Public Function myCDate(strDate)
If IsDate(strDate) Then myCDate = CDate(strDate)
End Function

And then:
"WHERE myCDate(tblBOM.ReleasedDate) Between #" & ...

I think you have nothing special to do for real DateTime fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanders720]

PHV:

Thanks, I'll try it out tomorrow. Your help is greatly appreciated.

 

[sanders720]

When I do the function, I get an error saying the function is undefined. I put it in exactly as you wrote. I noticed that in the WHERE statement, the mycdate did not capitalize. Is there another thing I need to do here. Sorry, but I'm not well versed on functions.

Thanks in advance again, for your help.

 

[sanders720]

moved to top.

The Query works out, but the function is noit recodnized in the query. Do I need to be doing something different as fac as getting the query to recognize the function?\

Thanks again for any help that can be provided.

SELECT tblBOM.JobNo, tblBOM.SubAssy, tblBOM.PartNo, tblPartsListing.PartDescription, tblPartsListing.ManufacturedBy, tblPartsListing.Code, tblPartsListing.RevisionLevel, tblBOM.ReleasedBy, tblBOM.ReleasedDate, tblBOM.QTY FROM tblPartsListing INNER JOIN tblBOM ON tblPartsListing.PartNo = tblBOM.PartNo WHERE myCDate(tblBOM.ReleasedDate) Between #9/1/2004# AND #9/30/2004#

Code:

sqlBOM = "SELECT tblBOM.JobNo, tblBOM.SubAssy, tblBOM.PartNo, tblPartsListing.PartDescription, tblPartsListing.ManufacturedBy, " & _
"tblPartsListing.Code, tblPartsListing.RevisionLevel, tblBOM.ReleasedBy, tblBOM.ReleasedDate, tblBOM.QTY " & _
"FROM tblPartsListing INNER JOIN tblBOM ON tblPartsListing.PartNo = tblBOM.PartNo " & _
"WHERE myCDate(tblBOM.ReleasedDate) Between #" & Forms!frmReportsListing.txtStartDate & "# AND #" & Forms!frmReportsListing.txtEndDate & "#

 

[sanders720]

moved to top - still hoping for some help if anyone can help.

Thanks.

 

[PHV]

Is the function recognized in the query window ?
How is your sqlBOM string used in your code ?
Anyway you may try this:
WHERE CDate(Nz(tblBOM.ReleasedDate,'01/01/1900')) Between

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanders720]

Sorry, still produced a Data Type Mismatch in Criteria Expression error.

The function called myCDate in itself works great, it just isn't being called right from the query. Is this normal?

Thanks for your reply.