Re: Invalid use of Null error question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
Tony
Douglas J. Steele wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

.

Relevant Pages

  • Re: Invalid use of Null error question
    ... string variable if nothing's been selected: string variables cannot be set ... only store numbers (Byte, Integer and Long can only store integer values, ... Variant is another type of variable. ... Dim strsql As String ...
    (microsoft.public.access.formscoding)
  • Re: Invalid use of Null error question
    ... string variable if nothing's been selected: string variables cannot be set ... only store numbers (Byte, Integer and Long can only store integer values, ... Variant is another type of variable. ... Dim strsql As String ...
    (microsoft.public.access.formscoding)
  • Re: Invalid use of Null error question
    ... Dim dtmFirstDay As Date ... Dim strsql As String ... string variable if nothing's been selected: string variables cannot be set ... only store numbers (Byte, Integer and Long can only store integer values, ...
    (microsoft.public.access.formscoding)
  • Re: Help with Hurricane Database
    ... Are you intending to reset the form's ... Doug Steele, Microsoft Access MVP ... As for the strSQL, that's what I should use to get user input for the ... form has the store information and the sub form has the data entry ...
    (microsoft.public.access.formscoding)
  • RE: IAzClientContext AccessCheck returns 0x80070057
    ... I get a successful access check. ... Could you post a text representation of your XML store? ... > VARIANT varOperationIdArray; ...
    (microsoft.public.platformsdk.security)