Prev: Demo can not work as expected
Next: RecordSource in VBA (Again)
From: Tony Williams on 21 Dec 2009 07:58
Can someone tell me why I'm getting an Invalid use of Null error message with
this code please.
Thanks
Tony
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# AND
([txtcompany] = '" & strtxtcompany & "' OR '" & strtxtcompany & "' IS Null);"
From: Douglas J. Steele on 21 Dec 2009 08:09
You appear to have included extra single quotes around strtxtcompany in the
IS Null comparison. However, there's really no point in including the IS
Null in the SQL. Try:

If IsNull(strtxtcompany) Then
Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblmaintabs] " & _
"WHERE [txtmonthlabel] = '" & Format(strtxtdate, "mmmm/yyyy") & "'"
Else
Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblmaintabs] " & _
"WHERE [txtmonthlabel] = '" & Format(strtxtdate, "mmmm/yyyy") & "' " & _
"AND [txtcompany] = '" & strtxtcompany & "'"
End If

Note that I've removed the # from the SQL. # is only used as a delimiter
when dealing with dates, and mmmm/yyyy is NOT a date.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message
news:7850DA7E-AD68-481B-86F1-1065BE5AEAA6(a)microsoft.com...
> Can someone tell me why I'm getting an Invalid use of Null error message
> with
> this code please.
> Thanks
> Tony
> Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
> WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# AND
> ([txtcompany] = '" & strtxtcompany & "' OR '" & strtxtcompany & "' IS
> Null);"


From: Tony Williams on 21 Dec 2009 12:50
Thanks Douglas.
Results: If I select a company and a date I get an error message that says
Data type mismatch in criteria expression.
If I just select date I still get Invalid use of Null.
Sorry for the delay in replying but I've not been at my PC this afternoon.

Thanks again
Tony
"Douglas J. Steele" wrote:

> You appear to have included extra single quotes around strtxtcompany in the
> IS Null comparison. However, there's really no point in including the IS
> Null in the SQL. Try:
>
> If IsNull(strtxtcompany) Then
> Forms!frmMain!SubForm1.Form.RecordSource = _
> "SELECT * FROM [tblmaintabs] " & _
> "WHERE [txtmonthlabel] = '" & Format(strtxtdate, "mmmm/yyyy") & "'"
> Else
> Forms!frmMain!SubForm1.Form.RecordSource = _
> "SELECT * FROM [tblmaintabs] " & _
> "WHERE [txtmonthlabel] = '" & Format(strtxtdate, "mmmm/yyyy") & "' " & _
> "AND [txtcompany] = '" & strtxtcompany & "'"
> End If
>
> Note that I've removed the # from the SQL. # is only used as a delimiter
> when dealing with dates, and mmmm/yyyy is NOT a date.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message
> news:7850DA7E-AD68-481B-86F1-1065BE5AEAA6(a)microsoft.com...
> > Can someone tell me why I'm getting an Invalid use of Null error message
> > with
> > this code please.
> > Thanks
> > Tony
> > Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
> > WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# AND
> > ([txtcompany] = '" & strtxtcompany & "' OR '" & strtxtcompany & "' IS
> > Null);"
>
>
> .
>
From: Tony Williams on 21 Dec 2009 13:00
Douglas, I apologise for my insistence about the mmmm/yyyy but with ' around
the field I get Data mismatch with # around the field the select query works
just fine. BUT I'm still getting the Inavlid use of Null with your amended
code?
Thanks again Reaaly appreciate your help.
Tony

"Douglas J. Steele" wrote:

> You appear to have included extra single quotes around strtxtcompany in the
> IS Null comparison. However, there's really no point in including the IS
> Null in the SQL. Try:
>
> If IsNull(strtxtcompany) Then
> Forms!frmMain!SubForm1.Form.RecordSource = _
> "SELECT * FROM [tblmaintabs] " & _
> "WHERE [txtmonthlabel] = '" & Format(strtxtdate, "mmmm/yyyy") & "'"
> Else
> Forms!frmMain!SubForm1.Form.RecordSource = _
> "SELECT * FROM [tblmaintabs] " & _
> "WHERE [txtmonthlabel] = '" & Format(strtxtdate, "mmmm/yyyy") & "' " & _
> "AND [txtcompany] = '" & strtxtcompany & "'"
> End If
>
> Note that I've removed the # from the SQL. # is only used as a delimiter
> when dealing with dates, and mmmm/yyyy is NOT a date.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message
> news:7850DA7E-AD68-481B-86F1-1065BE5AEAA6(a)microsoft.com...
> > Can someone tell me why I'm getting an Invalid use of Null error message
> > with
> > this code please.
> > Thanks
> > Tony
> > Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
> > WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# AND
> > ([txtcompany] = '" & strtxtcompany & "' OR '" & strtxtcompany & "' IS
> > Null);"
>
>
> .
>
From: BruceM via AccessMonster.com on 21 Dec 2009 14:49
What is txtmonthlabel? It seems to be a field in tblMainTabs, but what is it?
Is strtxtdate a string variable? If so, where does it come from? Likewise,
what is strtxtcompany?

I would try a Debug.Print after setting up the string. Quotes can be tricky.
You may need to double the quotes around in the formatted date (Format
(strtxtdate, ""mmmm/yyyy""), but better may be to define the formatted date
as a string variable:

Dim strMonYr as String
strMonYear = Format(strtxtdate, "mmmm/yyyy")

When I have trouble with an assembled string I sometimes use hard-coded
values rather than variables or values from controls. If it works, I
substitute variables one at a time. Also, start with the simplest possible
SQL string, then add one condition at a time.

Tony Williams wrote:
>Douglas, I apologise for my insistence about the mmmm/yyyy but with ' around
>the field I get Data mismatch with # around the field the select query works
>just fine. BUT I'm still getting the Inavlid use of Null with your amended
>code?
>Thanks again Reaaly appreciate your help.
>Tony
>
>> You appear to have included extra single quotes around strtxtcompany in the
>> IS Null comparison. However, there's really no point in including the IS
>[quoted text clipped - 25 lines]
>>
>> .

--
Message posted via http://www.accessmonster.com

 |  Next  |  Last
Pages: 1 2 3 4 5 6 7
Prev: Demo can not work as expected
Next: RecordSource in VBA (Again)