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

.

 

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

.

 

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


.

.

 

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

.

 

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

.

 

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

.

 

[Douglas J. Steele]

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.

There are many different types of variables in Access. Numeric variables can
only store numbers (Byte, Integer and Long can only store integer values,
Single and Double can store real values). String variables can store
alphanumeric data. Variant is another type of variable. Variables declared
as the Variant data type can contain string, date, time, Boolean, or numeric
values, and can convert the values they contain automatically. Numeric
Variant values require 16 bytes of memory (which is significant only in
large procedures or complex modules) and they are slower to access than
explicitly typed variables of any other type.

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.

How the data is stored and how it's presented to the user are two very
different things.

If txtmonthlabel is a date field in your table, then it contains a date,
which is a specific point in time (a day, month and year). Using Format, you
can display that date as simply a year, or a month and year, or simply a day
of the week if you want.

Under the covers, a date field is an eight byte floating point number where
the integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day. Hopefully that gives you an idea of why December, 2009 isn't a date:
what number would you store to represent how many days it's been since 30
Dec, 1899?

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

What is actually typed into Me.txtmontha: a full date, or only a month and
year?

One problem with your code is the line

strtxtcompany = Me.cmbselectcompany

There is no need to assign the value of the combo box to a variable: just
refer to the combo box in your code.

If me.txtmontha contains a full date, you could use something like:

Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim dtmFirstDay As Date
Dim dtmLastDay As Date
Dim strsql As String

If IsNull(Me.txtmontha) Then
Msgbox "You must supply a date."
Else
dtmFirstDay = DateSerial(Year(Me.txtmontha), Month(Me.txtmontha), 1)
dtmLastDay = DateSerial(Year(Me.txtmontha), Month(Me.txtmontha) + 1, 0)

strsql = "SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") "

If Len(Me.cmbselectcompany & vbNullString) > 0 Then
strsql = strsql & _
"AND [txtcompany] = '" & Me.cmbselectcompany & "'"
End If

Debug.Print strsql

Forms!frmMain!SubForm1.SourceObject = "subformFDA"
Forms!frmMain!SubForm1.Form.RecordSource = strsql
End If

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End Sub

 

[Douglas J. Steele]

Exactly what is printed to the Debug window?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Incidently following on from my post on the Irish forms, the Debug.Print
of
the sql shows that the sql is selecting the correct company and date but
it
isn't populating the form when it's open?
Tony

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.

There are many different types of variables in Access. Numeric variables
can
only store numbers (Byte, Integer and Long can only store integer values,
Single and Double can store real values). String variables can store
alphanumeric data. Variant is another type of variable. Variables
declared
as the Variant data type can contain string, date, time, Boolean, or
numeric
values, and can convert the values they contain automatically. Numeric
Variant values require 16 bytes of memory (which is significant only in
large procedures or complex modules) and they are slower to access than
explicitly typed variables of any other type.

The other problem is the one of dates. I'm not sure I understand the
problem

[quoted text clipped - 7 lines]

just put in a date that's when I get the Invalid use of Null' So I'm
beginning to feel lost.

How the data is stored and how it's presented to the user are two very
different things.

If txtmonthlabel is a date field in your table, then it contains a date,
which is a specific point in time (a day, month and year). Using Format,
you
can display that date as simply a year, or a month and year, or simply a
day
of the week if you want.

Under the covers, a date field is an eight byte floating point number
where
the integer portion represents the date as the number of days relative to
30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day. Hopefully that gives you an idea of why December, 2009 isn't a date:
what number would you store to represent how many days it's been since 30
Dec, 1899?

I realise that this is a lot to ask but could you rewrite my code to
exactly

[quoted text clipped - 33 lines]

End Sub

What is actually typed into Me.txtmontha: a full date, or only a month and
year?

One problem with your code is the line

strtxtcompany = Me.cmbselectcompany

There is no need to assign the value of the combo box to a variable: just
refer to the combo box in your code.

If me.txtmontha contains a full date, you could use something like:

Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim dtmFirstDay As Date
Dim dtmLastDay As Date
Dim strsql As String

If IsNull(Me.txtmontha) Then
Msgbox "You must supply a date."
Else
dtmFirstDay = DateSerial(Year(Me.txtmontha), Month(Me.txtmontha), 1)
dtmLastDay = DateSerial(Year(Me.txtmontha), Month(Me.txtmontha) + 1,
0)

strsql = "SELECT * FROM [tblmaintabs] " & _
"WHERE ([txtmonthlabel] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") "

If Len(Me.cmbselectcompany & vbNullString) > 0 Then
strsql = strsql & _
"AND [txtcompany] = '" & Me.cmbselectcompany & "'"
End If

Debug.Print strsql

Forms!frmMain!SubForm1.SourceObject = "subformFDA"
Forms!frmMain!SubForm1.Form.RecordSource = strsql
End If

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End Sub

 

[Douglas J. Steele]

Copy the SQL statement SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel]
Between #2009-09-01# AND
#2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd' to the
clipboard.

Create a new query, but don't select any tables for it.

Switch the query to SQL view and paste the SQL statement into the SQL view
window.

Run the query.

Do you get any rows returned?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This:
24/12/2009 16:37:08 Loading Submenu from fLoadSubMenu() (debug logging
turned
off)
24/12/2009 16:37:08 Loading Submenu-Complete from fLoadSubMenu() (debug
logging turned off)
SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel] Between #2009-09-01#
AND
#2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd'


I selected September 2009 in the txtmontha combo and AIB Commercial
Services
Ltd in the company combo. So the sql appears to be interpreting the
correct
data but wont pass it to the form once it is opened.
Very odd?

Exactly what is printed to the Debug window?

Incidently following on from my post on the Irish forms, the Debug.Print
of

[quoted text clipped - 107 lines]

End Sub

 

[Douglas J. Steele]

I'm in Canada, so it's only 13:45 at the moment.

Let's resume after the Christmas break.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes Douglas I get the correct data for that company and that quarter so
the
sql is OK isn't it?
Tony
Incidently where are you, sorry to ask but I'm in the UK and it's 17.39 on
Xmas Eve and my wife is saying to me "Haven't you done enough?"

Copy the SQL statement SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel]
Between #2009-09-01# AND
#2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd' to the
clipboard.

Create a new query, but don't select any tables for it.

Switch the query to SQL view and paste the SQL statement into the SQL view
window.

Run the query.

Do you get any rows returned?

This:
24/12/2009 16:37:08 Loading Submenu from fLoadSubMenu() (debug logging

[quoted text clipped - 20 lines]

End Sub