Invalid use of Null error question
in [Forms]
|
From: TonyWilliams via AccessMonster.com on 23 Dec 2009 13:11 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 Douglas J. Steele wrote: >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] >>>>> >> >> >>>>> >> >> . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
From: Douglas J. Steele on 24 Dec 2009 09:51 "TonyWilliams via AccessMonster.com" <u56994(a)uwe> wrote in message news:a107409ef1633(a)uwe... > 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 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
From: TonyWilliams via AccessMonster.com on 24 Dec 2009 10:16 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
From: TonyWilliams via AccessMonster.com on 24 Dec 2009 10:38 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! 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
From: TonyWilliams via AccessMonster.com on 24 Dec 2009 10:42
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 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 |