Invalid use of Null error question
in [Forms]
|
From: Douglas J. Steele on 23 Dec 2009 07:34 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 http://I.Am/DougSteele (no e-mails, please!) "Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message news:25A14F91-7752-45C8-B5B2-761A5F0BB6D5(a)microsoft.com... > 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 > > "BruceM via AccessMonster.com" wrote: > >> 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 >> on our own initiative. >> >> Tony Williams wrote: >> >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] >> >> >> >> >> >> . >> >> -- >> Message posted via http://www.accessmonster.com >> >> . >>
From: TonyWilliams via AccessMonster.com on 23 Dec 2009 08:42 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 BruceM wrote: >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] >>> >> >>> >> . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
From: TonyWilliams via AccessMonster.com on 23 Dec 2009 08:55 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 Douglas J. Steele wrote: >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] >>> >> >> >>> >> >> . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
From: Douglas J. Steele on 23 Dec 2009 12:00 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 http://I.Am/DougSteele (no e-mails, please!) "TonyWilliams via AccessMonster.com" <u56994(a)uwe> wrote in message news:a1051cfa1a245(a)uwe... > 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 > > Douglas J. Steele wrote: >>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] >>>> >> >> >>>> >> >> . > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1 >
From: TonyWilliams via AccessMonster.com on 23 Dec 2009 13:00
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 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 |