Invalid use of Null error question

[TonyWilliams via AccessMonster.com]

Douglas I tried this (which is some earlier code you gave me) and got a
message to say expression refers to an object that is closed or doesn't exist?

Thanks Tony
Private Sub Command36_Click()
On Error GoTo Err_Command36_Click

Dim strtxtcompany As String
Dim strtxtdate As Date
Forms!frmMain!SubForm1.SourceObject = "subformFDA"
strtxtdate = Me.txtmontha
strtxtcompany = Me.cmbselectcompany
If IsNull(Me.cmbselectcompany) 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_Command36_Click:
Exit Sub

Err_Command36_Click:
MsgBox Err.Description
Resume Exit_Command36_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]

 

[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

 

[TonyWilliams via AccessMonster.com]

Thanks Douglas that's really helpful. I really need to study your post and
realte it to what I already know. I have read a number of books about VBA but
I never find reading as good as actually "doing"

As to my code - the value of txtnmontha is a date value selected from a combo
box and the values are shown as mmmm/yyyy The problem I have with this
database is that the data that is input all relates to quarters not
individual days of the month. So the dates that are stores in a table called
tblmonth (which is the the source of the txtmontha comobox) are say January
2009, March 2009, June 2009 and September 2009. In truth I realise that
although the format is mmmm/yyyy the underlying date is 01/01/09, 01/03/09,
01/06/09 and 01/09/09 but the user never inputs those dates. They only see
and work with dates in the mmmm/yyyy format.

With that in mind does it affect how you have written your code?
Many thanks

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

 

[TonyWilliams via AccessMonster.com]

If I knew where you were Douglas there'd be a crate of the best on its way to
you!

I have (part) success. That code worked absolutley fine for one of my tables
and forms. However I have another table and form which have the same fields
and controls as those in the original code, but all the data is in Euros
whereas the other table contains Sterling values. So I have created another
search form based on my original and just changed the source table and form
to the Irish table. The form opens when I click on the command button but
with a blank record. You may have seen another post of mine where I have
raised this. I have checked that there is no difference between the tables
and the forms apart from their names and the fact that they hold different
data. Here is the amended code and you can see where the differences are.
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 [tblmainIrish] " & _
"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 = "subformIrish"
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

Do you have any suggestions as to what may be the reason for this -so near
yet so far!
Once again many thanks for all your help and particularly patience!

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

 

[TonyWilliams via AccessMonster.com]

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]

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

 

[TonyWilliams via AccessMonster.com]

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]

 

[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

 

[TonyWilliams via AccessMonster.com]

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]

 

[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

 

[TonyWilliams via AccessMonster.com]

OK Douglas thank you and have a really good festive break!
Really appreciate all you've done.
Bye for now
Tony

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

Let's resume after the Christmas break.

Yes Douglas I get the correct data for that company and that quarter so
the

[quoted text clipped - 21 lines]