Invalid use of Null error question
in [Forms]
|
From: Tony Williams on 22 Dec 2009 04:30 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 "BruceM via AccessMonster.com" wrote: > 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, > what is strtxtcompany? > > I would try a Debug.Print after setting up the string. Quotes can be tricky. > You may need to double the quotes around in the formatted date (Format > (strtxtdate, ""mmmm/yyyy""), but better may be to define the formatted date > as a string variable: > > Dim strMonYr as String > strMonYear = Format(strtxtdate, "mmmm/yyyy") > > When I have trouble with an assembled string I sometimes use hard-coded > values rather than variables or values from controls. If it works, I > substitute variables one at a time. Also, start with the simplest possible > SQL string, then add one condition at a time. > > Tony Williams wrote: > >Douglas, I apologise for my insistence about the mmmm/yyyy but with ' around > >the field I get Data mismatch with # around the field the select query works > >just fine. BUT I'm still getting the Inavlid use of Null with your amended > >code? > >Thanks again Reaaly appreciate your help. > >Tony > > > >> You appear to have included extra single quotes around strtxtcompany in the > >> IS Null comparison. However, there's really no point in including the IS > >[quoted text clipped - 25 lines] > >> > >> . > > -- > Message posted via http://www.accessmonster.com > > . >
From: Douglas J. Steele on 22 Dec 2009 08:13 Exactly what is stored in txtmonthlabel (and what's in strtxtdate)? If txtmonthlabel is actually a date/time field, and you're inputting a date in strtxtdate, but only want all dates in that same month, use something like "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 & "'" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message news:4A69B711-F8F6-42CB-A4C9-8F2DB31B1193(a)microsoft.com... > Douglas, I apologise for my insistence about the mmmm/yyyy but with ' > around > the field I get Data mismatch with # around the field the select query > works > just fine. BUT I'm still getting the Inavlid use of Null with your amended > code? > Thanks again Reaaly appreciate your help. > Tony > > "Douglas J. Steele" wrote: > >> You appear to have included extra single quotes around strtxtcompany in >> the >> IS Null comparison. However, there's really no point in including the IS >> Null in the SQL. Try: >> >> 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 >> >> Note that I've removed the # from the SQL. # is only used as a delimiter >> when dealing with dates, and mmmm/yyyy is NOT a date. >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no private e-mails, please) >> >> >> "Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message >> news:7850DA7E-AD68-481B-86F1-1065BE5AEAA6(a)microsoft.com... >> > Can someone tell me why I'm getting an Invalid use of Null error >> > message >> > with >> > this code please. >> > Thanks >> > Tony >> > Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs] >> > WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# AND >> > ([txtcompany] = '" & strtxtcompany & "' OR '" & strtxtcompany & "' IS >> > Null);" >> >> >> . >>
From: BruceM via AccessMonster.com on 22 Dec 2009 09:21 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 22 Dec 2009 09:25 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 "Douglas J. Steele" wrote: > Exactly what is stored in txtmonthlabel (and what's in strtxtdate)? > > If txtmonthlabel is actually a date/time field, and you're inputting a date > in strtxtdate, but only want all dates in that same month, use something > like > > "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 & "'" > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message > news:4A69B711-F8F6-42CB-A4C9-8F2DB31B1193(a)microsoft.com... > > Douglas, I apologise for my insistence about the mmmm/yyyy but with ' > > around > > the field I get Data mismatch with # around the field the select query > > works > > just fine. BUT I'm still getting the Inavlid use of Null with your amended > > code? > > Thanks again Reaaly appreciate your help. > > Tony > > > > "Douglas J. Steele" wrote: > > > >> You appear to have included extra single quotes around strtxtcompany in > >> the > >> IS Null comparison. However, there's really no point in including the IS > >> Null in the SQL. Try: > >> > >> 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 > >> > >> Note that I've removed the # from the SQL. # is only used as a delimiter > >> when dealing with dates, and mmmm/yyyy is NOT a date. > >> > >> -- > >> Doug Steele, Microsoft Access MVP > >> http://I.Am/DougSteele > >> (no private e-mails, please) > >> > >> > >> "Tony Williams" <TonyWilliams(a)discussions.microsoft.com> wrote in message > >> news:7850DA7E-AD68-481B-86F1-1065BE5AEAA6(a)microsoft.com... > >> > Can someone tell me why I'm getting an Invalid use of Null error > >> > message > >> > with > >> > this code please. > >> > Thanks > >> > Tony > >> > Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs] > >> > WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# AND > >> > ([txtcompany] = '" & strtxtcompany & "' OR '" & strtxtcompany & "' IS > >> > Null);" > >> > >> > >> . > >> > > > . >
From: BruceM via AccessMonster.com on 22 Dec 2009 11:13
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 |