Invalid use of Null error question

[Tony Williams]

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);"

 

[Douglas J. Steele]

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.

 

[Tony Williams]

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

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

(no private e-mails, please)

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);"

.

 

[Tony Williams]

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
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

(no private e-mails, please)

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);"

.

 

[BruceM via AccessMonster.com]

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.

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]

.

 

[Tony Williams]

Hi Bruce, here is the actual code behind the command button:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click

Dim strtxtcompany As String
Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
strtxtcompany = Me.cmbselectcompany.Value
Forms!frmMain!SubForm1.SourceObject = "subformFDA"

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


Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub


txtmonthlabel is a field in tblmaintabs the two "str" expressions are
defined at the top of the code. Both are controls on my form that holds the
control button.

I'm something of a beginner at VBA how do I use Debug.Print?
Thanks for your help
Tony

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.

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]

.

 

[Douglas J. Steele]

Exactly what is stored in txtmonthlabel (and what's in strtxtdate)?

If txtmonthlabel is actually a date/time field, and you're inputting a date
in strtxtdate, but only want all dates in that same month, use something
like

"SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] >= " & _
Format(DateSerial(Year(strtxtdate), Month(strtxtdate), 1),
"\#yyyy\-mm\-dd\#")
" AND [txtmonthlabel] <= " & _

Format(DateSerial(Year(strtxtdate), Month(strtxtdate) + 1, 0),
"\#yyyy\-mm\-dd\#") & ")"
"AND [txtcompany] = '" & strtxtcompany & "'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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
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

(no private e-mails, please)

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);"

.

 

[BruceM via AccessMonster.com]

I would do something like this for the RecordSource (using a somewhat
modified version of the code Douglas provided in his most recent posting):

Dim strSQL as String
Dim strtxtcompany As String
Dim dattxtdate As Date
dattxtdate = Me.txtmontha
strtxtcompany = Me.cmbselectcompany

strSQL = "SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] >= " & _
DateSerial(Year(dattxtdate), Month(dattxtdate), 1) & _
" AND [txtmonthlabel] <= " & _
DateSerial(Year(dattxtdate), Month(dattxtdate) + 1, 0) & _
") AND [txtcompany] = ' " & strtxtcompany & " ' "

Debug.Print strSQL

Me.RecordSource = strSQL

I would use a different prefix for different types of variables: str for
String, dat for Date, var for Variant, and so forth. I have shown that with
dattxtdate. Makes it easier to tell things apart, IMO, but it's your choice.

Anyhow, after running the code, open the VBA editor immediate window by
pressing Ctrl + G (there are other ways, but that is probably the simplest).
In the immediate window you will see the string being used for the record
source. I was incorrect about doubling the quotes, by the way. I should
have tested, but I was a bit low on time.

Another use of the immediate window is to test code. For instance, type the
following (including the question mark) and press Enter:

?Date() = Format(Date(),"\#mmmm/yyyy\#)

or

?Date() = Format(Date(),"dd/mm/yy")

It will return False in either case. If you substitute the values in
txtmonthlabel (assuming txtmonthlabel is a date) for Date(), likewise it will
return False. The point is that comparing a date value to a date formatted
with the Format function will result in False, even if you and I can see it
is True. Access is very literal in that way.

Hi Bruce, here is the actual code behind the command button:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click

Dim strtxtcompany As String
Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
strtxtcompany = Me.cmbselectcompany.Value
Forms!frmMain!SubForm1.SourceObject = "subformFDA"

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

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

txtmonthlabel is a field in tblmaintabs the two "str" expressions are
defined at the top of the code. Both are controls on my form that holds the
control button.

I'm something of a beginner at VBA how do I use Debug.Print?
Thanks for your help
Tony

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,

[quoted text clipped - 25 lines]

 

[Tony Williams]

Thanks Douglas but when I pasted that code into my procedure it was just all
red with messages about syntax errors and expected end of statements.

I'm not all that experienced at VBA so could you give me some pointers as to
where I should start with amending my original code?
Thanks for your help
Tony

Exactly what is stored in txtmonthlabel (and what's in strtxtdate)?

If txtmonthlabel is actually a date/time field, and you're inputting a date
in strtxtdate, but only want all dates in that same month, use something
like

"SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] >= " & _
Format(DateSerial(Year(strtxtdate), Month(strtxtdate), 1),
"\#yyyy\-mm\-dd\#")
" AND [txtmonthlabel] <= " & _

Format(DateSerial(Year(strtxtdate), Month(strtxtdate) + 1, 0),
"\#yyyy\-mm\-dd\#") & ")"
"AND [txtcompany] = '" & strtxtcompany & "'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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
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

(no private e-mails, please)


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);"


.

.

 

[BruceM via AccessMonster.com]

Beware of line wrapping in newsgroup messages. Note that some lines end with
ambersand space underscore. The underscore (preceded by a space) is a line
continuation character. The ampersand is the concatenation operator, which
is to say when assembling a string you need the ampersand between the parts
of the string. In an expression you could just use space underscore.
Something like this:

"SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] >= " & _
Format(DateSerial(Year(strtxtdate), Month(strtxtdate), 1), _
"\#yyyy\-mm\-dd\#") & _
" AND [txtmonthlabel] <= " & _
Format(DateSerial(Year(strtxtdate), Month(strtxtdate) + 1, 0), _
"\#yyyy\-mm\-dd\#") & ") " & _
"AND [txtcompany] = '" & strtxtcompany & "'"

Thanks Douglas but when I pasted that code into my procedure it was just all
red with messages about syntax errors and expected end of statements.

I'm not all that experienced at VBA so could you give me some pointers as to
where I should start with amending my original code?
Thanks for your help
Tony

Exactly what is stored in txtmonthlabel (and what's in strtxtdate)?

[quoted text clipped - 55 lines]

.

 

[Tony Williams]

Thanks Bruce. Got rid of the error messages but am still getting inavalid use
of null message with Douglas' code?
I think unless you have any further ideas I may have to rethink my process I
don't want to take any more time up from you guys.
Wistfully yours
Tony

Beware of line wrapping in newsgroup messages. Note that some lines end with
ambersand space underscore. The underscore (preceded by a space) is a line
continuation character. The ampersand is the concatenation operator, which
is to say when assembling a string you need the ampersand between the parts
of the string. In an expression you could just use space underscore.
Something like this:

"SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] >= " & _
Format(DateSerial(Year(strtxtdate), Month(strtxtdate), 1), _
"\#yyyy\-mm\-dd\#") & _
" AND [txtmonthlabel] <= " & _
Format(DateSerial(Year(strtxtdate), Month(strtxtdate) + 1, 0), _
"\#yyyy\-mm\-dd\#") & ") " & _
"AND [txtcompany] = '" & strtxtcompany & "'"

Thanks Douglas but when I pasted that code into my procedure it was just all
red with messages about syntax errors and expected end of statements.

I'm not all that experienced at VBA so could you give me some pointers as to
where I should start with amending my original code?
Thanks for your help
Tony

Exactly what is stored in txtmonthlabel (and what's in strtxtdate)?

[quoted text clipped - 55 lines]

.

 

[BruceM via AccessMonster.com]

Take a look at an earlier thread when I described Debug.Print. I used
similar code, but without the formatting.

Does the Invalid Use of Null give you a chance to debug the code? If so,
what line is highlighted?

Stepping through the code can be helpful. Click the vertical bar to the left
of the code. This should place a large dot in the bar, and highlight the
line of code. Go to the form and try running the code. When it reaches the
marked line of code, press the F8 key to step through the code one line at a
time. This should allow you to narrow down where the error occurs.

Don't worry about taking up the time of those who respond. We're doing this

Thanks Bruce. Got rid of the error messages but am still getting inavalid use
of null message with Douglas' code?
I think unless you have any further ideas I may have to rethink my process I
don't want to take any more time up from you guys.
Wistfully yours
Tony

Beware of line wrapping in newsgroup messages. Note that some lines end with
ambersand space underscore. The underscore (preceded by a space) is a line

[quoted text clipped - 25 lines]

 

[Douglas J. Steele]

That won't work, Bruce. The dates have to be formatted and delimited with #.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I would do something like this for the RecordSource (using a somewhat
modified version of the code Douglas provided in his most recent posting):

Dim strSQL as String
Dim strtxtcompany As String
Dim dattxtdate As Date
dattxtdate = Me.txtmontha
strtxtcompany = Me.cmbselectcompany

strSQL = "SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] >= " & _
DateSerial(Year(dattxtdate), Month(dattxtdate), 1) & _
" AND [txtmonthlabel] <= " & _
DateSerial(Year(dattxtdate), Month(dattxtdate) + 1, 0) & _
") AND [txtcompany] = ' " & strtxtcompany & " ' "

Debug.Print strSQL

Me.RecordSource = strSQL

I would use a different prefix for different types of variables: str for
String, dat for Date, var for Variant, and so forth. I have shown that
with
dattxtdate. Makes it easier to tell things apart, IMO, but it's your
choice.

Anyhow, after running the code, open the VBA editor immediate window by
pressing Ctrl + G (there are other ways, but that is probably the
simplest).
In the immediate window you will see the string being used for the record
source. I was incorrect about doubling the quotes, by the way. I should
have tested, but I was a bit low on time.

Another use of the immediate window is to test code. For instance, type
the
following (including the question mark) and press Enter:

?Date() = Format(Date(),"\#mmmm/yyyy\#)

or

?Date() = Format(Date(),"dd/mm/yy")

It will return False in either case. If you substitute the values in
txtmonthlabel (assuming txtmonthlabel is a date) for Date(), likewise it
will
return False. The point is that comparing a date value to a date
formatted
with the Format function will result in False, even if you and I can see
it
is True. Access is very literal in that way.

Hi Bruce, here is the actual code behind the command button:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click

Dim strtxtcompany As String
Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
strtxtcompany = Me.cmbselectcompany.Value
Forms!frmMain!SubForm1.SourceObject = "subformFDA"

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

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

txtmonthlabel is a field in tblmaintabs the two "str" expressions are
defined at the top of the code. Both are controls on my form that holds
the
control button.

I'm something of a beginner at VBA how do I use Debug.Print?
Thanks for your help
Tony

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,

[quoted text clipped - 25 lines]

.

 

[Tony Williams]

Hi Bruce, firstly thanks for sticking with me!

I don't get chance to Debug the code, the VBA window doesn't open. I just
get a message box that says Invalid use of Null so I can't tell which line is
causing the problem.

I searched on Debug.Print in this group but found hundreds of posts, could
you narrow it down for me?
Thanks
and Merry Christmas!
Tony

Take a look at an earlier thread when I described Debug.Print. I used
similar code, but without the formatting.

Does the Invalid Use of Null give you a chance to debug the code? If so,
what line is highlighted?

Stepping through the code can be helpful. Click the vertical bar to the left
of the code. This should place a large dot in the bar, and highlight the
line of code. Go to the form and try running the code. When it reaches the
marked line of code, press the F8 key to step through the code one line at a
time. This should allow you to narrow down where the error occurs.

Don't worry about taking up the time of those who respond. We're doing this

Thanks Bruce. Got rid of the error messages but am still getting inavalid use
of null message with Douglas' code?
I think unless you have any further ideas I may have to rethink my process I
don't want to take any more time up from you guys.
Wistfully yours
Tony

Beware of line wrapping in newsgroup messages. Note that some lines end with
ambersand space underscore. The underscore (preceded by a space) is a line

[quoted text clipped - 25 lines]

.

 

[Tony Williams]

Bruce did some research on Debug .Print and I put breaks in the code. When I
clicked the command button with the cmbselectcompany control blank, the VBA
window opened so I could see what values each of the controls had. The date
control was OK but the cmbselectcompany control showed as equalling Null so
that is obviously where the fault is the code doesn't like
cmbselectcompany=Null
That any help?
Thanks again
Tony

Take a look at an earlier thread when I described Debug.Print. I used
similar code, but without the formatting.

Does the Invalid Use of Null give you a chance to debug the code? If so,
what line is highlighted?

Stepping through the code can be helpful. Click the vertical bar to the left
of the code. This should place a large dot in the bar, and highlight the
line of code. Go to the form and try running the code. When it reaches the
marked line of code, press the F8 key to step through the code one line at a
time. This should allow you to narrow down where the error occurs.

Don't worry about taking up the time of those who respond. We're doing this

Thanks Bruce. Got rid of the error messages but am still getting inavalid use
of null message with Douglas' code?
I think unless you have any further ideas I may have to rethink my process I
don't want to take any more time up from you guys.
Wistfully yours
Tony

Beware of line wrapping in newsgroup messages. Note that some lines end with
ambersand space underscore. The underscore (preceded by a space) is a line

[quoted text clipped - 25 lines]

.

 

[Douglas J. Steele]

Where do you have cmbselectcompany = Null?

I don't see it in any of the code you've posted so far...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Bruce did some research on Debug .Print and I put breaks in the code. When
I
clicked the command button with the cmbselectcompany control blank, the
VBA
window opened so I could see what values each of the controls had. The
date
control was OK but the cmbselectcompany control showed as equalling Null
so
that is obviously where the fault is the code doesn't like
cmbselectcompany=Null
That any help?
Thanks again
Tony

Take a look at an earlier thread when I described Debug.Print. I used
similar code, but without the formatting.

Does the Invalid Use of Null give you a chance to debug the code? If so,
what line is highlighted?

Stepping through the code can be helpful. Click the vertical bar to the
left
of the code. This should place a large dot in the bar, and highlight the
line of code. Go to the form and try running the code. When it reaches
the
marked line of code, press the F8 key to step through the code one line
at a
time. This should allow you to narrow down where the error occurs.

Don't worry about taking up the time of those who respond. We're doing
this

Thanks Bruce. Got rid of the error messages but am still getting
inavalid use
of null message with Douglas' code?
I think unless you have any further ideas I may have to rethink my
process I
don't want to take any more time up from you guys.
Wistfully yours
Tony

Beware of line wrapping in newsgroup messages. Note that some lines
end with
ambersand space underscore. The underscore (preceded by a space) is a
line
[quoted text clipped - 25 lines]

.

 

[TonyWilliams via AccessMonster.com]

Hi Bruce picked up this message on Access Monster I've been monitoring
Microsoft Groups and sorry missed the comments you made about Debug.Print.
I've now tried that and whilst I get the correct data in the Immediate Window
when I complete both date and company i still get Invalid use of Null with
just the date and nothing shows in the Immediate Window.
I hope I'm not stretching your patience too much!
Regards
Tony

I would do something like this for the RecordSource (using a somewhat
modified version of the code Douglas provided in his most recent posting):

Dim strSQL as String
Dim strtxtcompany As String
Dim dattxtdate As Date
dattxtdate = Me.txtmontha
strtxtcompany = Me.cmbselectcompany

strSQL = "SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] >= " & _
DateSerial(Year(dattxtdate), Month(dattxtdate), 1) & _
" AND [txtmonthlabel] <= " & _
DateSerial(Year(dattxtdate), Month(dattxtdate) + 1, 0) & _
") AND [txtcompany] = ' " & strtxtcompany & " ' "

Debug.Print strSQL

Me.RecordSource = strSQL

I would use a different prefix for different types of variables: str for
String, dat for Date, var for Variant, and so forth. I have shown that with
dattxtdate. Makes it easier to tell things apart, IMO, but it's your choice.

Anyhow, after running the code, open the VBA editor immediate window by
pressing Ctrl + G (there are other ways, but that is probably the simplest).
In the immediate window you will see the string being used for the record
source. I was incorrect about doubling the quotes, by the way. I should
have tested, but I was a bit low on time.

Another use of the immediate window is to test code. For instance, type the
following (including the question mark) and press Enter:

?Date() = Format(Date(),"\#mmmm/yyyy\#)

or

?Date() = Format(Date(),"dd/mm/yy")

It will return False in either case. If you substitute the values in
txtmonthlabel (assuming txtmonthlabel is a date) for Date(), likewise it will
return False. The point is that comparing a date value to a date formatted
with the Format function will result in False, even if you and I can see it
is True. Access is very literal in that way.

Hi Bruce, here is the actual code behind the command button:
Private Sub cmdopenrecord_Click()

[quoted text clipped - 39 lines]

 

[TonyWilliams via AccessMonster.com]

Hi Douglas I used the Break in VBA to see what the values were as the code
ran and that's where I could see cmbselect=null.
I've been palying around with the code since using the comments from yoursel
and Bruce and this is where it's at at the moment:
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click
Dim strsql As String
Dim strtxtcompany As String
Dim dattxtdate As Date

dattxtdate = Me.txtmontha
strtxtcompany = Me.cmbselectcompany
strsql = "SELECT * FROM [tblmaintabs] " & _
"WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
"AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
"AND [txtcompany] = '" & strtxtcompany & "'"
Forms!frmMain!SubForm1.SourceObject = "subformFDA"

Forms!frmMain!SubForm1.Form.RecordSource = strsql

Debug.Print strsql


Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End Sub
BUT it works if I choose a company in cmbselectcompany and a date in
txtmontha BUT I still get Invalid use of Null message box if I only choose a
date and leavr cmbselectcompany blank.

Thanks for sticking with me and a Merry Christmas!
Regards
Tony

Where do you have cmbselectcompany = Null?

I don't see it in any of the code you've posted so far...

Bruce did some research on Debug .Print and I put breaks in the code. When
I

[quoted text clipped - 45 lines]

 

[Douglas J. Steele]

You cannot assign its value to a string variable if nothing's been selected:
string variables cannot be set to Null (the only variable type that can is
Variant)

I gave you the answer days ago: you must check

If IsNull(Me.cmbselectcompany) Then
' Set strsql without reference to cmbselectcompany
Else
' Set strsql including cmbselectcompany
End If

And please listen to me when I say that you cannot use # delimiters unless
you're using a complete date, and Format(dattxtdate, "mmmm/yyyy") isn't a
complete date!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas I used the Break in VBA to see what the values were as the code
ran and that's where I could see cmbselect=null.
I've been palying around with the code since using the comments from
yoursel
and Bruce and this is where it's at at the moment:
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click
Dim strsql As String
Dim strtxtcompany As String
Dim dattxtdate As Date

dattxtdate = Me.txtmontha
strtxtcompany = Me.cmbselectcompany
strsql = "SELECT * FROM [tblmaintabs] " & _
"WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
"AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
"AND [txtcompany] = '" & strtxtcompany & "'"
Forms!frmMain!SubForm1.SourceObject = "subformFDA"

Forms!frmMain!SubForm1.Form.RecordSource = strsql

Debug.Print strsql


Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End Sub
BUT it works if I choose a company in cmbselectcompany and a date in
txtmontha BUT I still get Invalid use of Null message box if I only choose
a
date and leavr cmbselectcompany blank.

Thanks for sticking with me and a Merry Christmas!
Regards
Tony

Where do you have cmbselectcompany = Null?

I don't see it in any of the code you've posted so far...

Bruce did some research on Debug .Print and I put breaks in the code.
When
I

[quoted text clipped - 45 lines]

.

 

[TonyWilliams via AccessMonster.com]

Douglas please first accept my apologies, I assure you I try to understand
what people write in here but sometimes I get buried in treacle. I'm no VBA
expert but trying to learn quickly (although at 65 my brain cells don't
always co-operate!)
So could you take me through your comment "You cannot assign its value to a
string variable if nothing's been selected: string variables cannot be set to
Null (the only variable type that can is Variant)" I'm afraid I just don't
understand that.

The other problem is the one of dates. I'm not sure I understand the problem
here. my field txmontha is a date field in my table and I only format it as
mmmm/yyyy because that's how the user wants to see it. When I used some
previous code you gave me without the # I got a message about type mismatch.
If I use # then the code works if I put in a company and date but not if I
just put in a date that's when I get the Invalid use of Null' So I'm
beginning to feel lost.

I realise that this is a lot to ask but could you rewrite my code to exactly
what it should be, it's not a cop out I'm just completely at a loss as to
where ot go from here.
With greatest thanks in anticipation
Tony

This is my code now:
Private Sub Command35_Click()
On Error GoTo Err_Command35_Click
Dim strsql As String
Dim strtxtcompany As String
Dim dattxtdate As Date

dattxtdate = Me.txtmontha
strtxtcompany = Me.cmbselectcompany
If IsNull(Me.cmbselectcompany) Then
strsql = "SELECT * FROM [tblmaintabs] " & _
"WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#"
Else
strsql = "SELECT * FROM [tblmaintabs] " & _
"Where [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
"AND [txtcompany] = '" & strtxtcompany & "'"
Forms!frmMain!SubForm1.SourceObject = "subformFDA"
End If
Forms!frmMain!SubForm1.Form.RecordSource = strsql

Debug.Print strsql


Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End Sub

You cannot assign its value to a string variable if nothing's been selected:
string variables cannot be set to Null (the only variable type that can is
Variant)

I gave you the answer days ago: you must check

If IsNull(Me.cmbselectcompany) Then
' Set strsql without reference to cmbselectcompany
Else
' Set strsql including cmbselectcompany
End If

And please listen to me when I say that you cannot use # delimiters unless
you're using a complete date, and Format(dattxtdate, "mmmm/yyyy") isn't a
complete date!

Hi Douglas I used the Break in VBA to see what the values were as the code
ran and that's where I could see cmbselect=null.

[quoted text clipped - 46 lines]