Invalid use of Null error question
in [Forms]
|
From: Tony Williams on 22 Dec 2009 13:23 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 "BruceM via AccessMonster.com" wrote: > 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 & "'" > > Tony Williams wrote: > >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] > >> > >> . > > -- > Message posted via http://www.accessmonster.com > > . >
From: BruceM via AccessMonster.com on 22 Dec 2009 14:11 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: Douglas J. Steele on 22 Dec 2009 16:42 That won't work, Bruce. The dates have to be formatted and delimited with #. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "BruceM via AccessMonster.com" <u54429(a)uwe> wrote in message news:a0f8c502677bf(a)uwe... >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. > > > Tony Williams wrote: >>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] >>> >> >>> >> . > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1 >
From: Tony Williams on 23 Dec 2009 07:06 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 "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: Tony Williams on 23 Dec 2009 07:29
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 > > . > |