Invalid use of Null error question
in [Forms]
|
From: Douglas J. Steele on 24 Dec 2009 11:26 Exactly what is printed to the Debug window? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "TonyWilliams via AccessMonster.com" <u56994(a)uwe> wrote in message news:a1129e456d14c(a)uwe... > 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 >
From: TonyWilliams via AccessMonster.com on 24 Dec 2009 11:40 This: 24/12/2009 16:37:08 Loading Submenu from fLoadSubMenu() (debug logging turned off) 24/12/2009 16:37:08 Loading Submenu-Complete from fLoadSubMenu() (debug logging turned off) SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel] Between #2009-09-01# AND #2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd' I selected September 2009 in the txtmontha combo and AIB Commercial Services Ltd in the company combo. So the sql appears to be interpreting the correct data but wont pass it to the form once it is opened. Very odd? Tony Douglas J. Steele wrote: >Exactly what is printed to the Debug window? > >> Incidently following on from my post on the Irish forms, the Debug.Print >> of >[quoted text clipped - 107 lines] >>> >>>End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
From: Douglas J. Steele on 24 Dec 2009 12:20 Copy the SQL statement SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel] Between #2009-09-01# AND #2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd' to the clipboard. Create a new query, but don't select any tables for it. Switch the query to SQL view and paste the SQL statement into the SQL view window. Run the query. Do you get any rows returned? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "TonyWilliams via AccessMonster.com" <u56994(a)uwe> wrote in message news:a1131f98bd511(a)uwe... > This: > 24/12/2009 16:37:08 Loading Submenu from fLoadSubMenu() (debug logging > turned > off) > 24/12/2009 16:37:08 Loading Submenu-Complete from fLoadSubMenu() (debug > logging turned off) > SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel] Between #2009-09-01# > AND > #2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd' > > > I selected September 2009 in the txtmontha combo and AIB Commercial > Services > Ltd in the company combo. So the sql appears to be interpreting the > correct > data but wont pass it to the form once it is opened. > Very odd? > Tony > Douglas J. Steele wrote: >>Exactly what is printed to the Debug window? >> >>> Incidently following on from my post on the Irish forms, the Debug.Print >>> of >>[quoted text clipped - 107 lines] >>>> >>>>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 12:39 Yes Douglas I get the correct data for that company and that quarter so the sql is OK isn't it? Tony Incidently where are you, sorry to ask but I'm in the UK and it's 17.39 on Xmas Eve and my wife is saying to me "Haven't you done enough?" Douglas J. Steele wrote: >Copy the SQL statement SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel] >Between #2009-09-01# AND >#2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd' to the >clipboard. > >Create a new query, but don't select any tables for it. > >Switch the query to SQL view and paste the SQL statement into the SQL view >window. > >Run the query. > >Do you get any rows returned? > >> This: >> 24/12/2009 16:37:08 Loading Submenu from fLoadSubMenu() (debug logging >[quoted text clipped - 20 lines] >>>>> >>>>>End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
From: Douglas J. Steele on 24 Dec 2009 13:46
I'm in Canada, so it's only 13:45 at the moment. Let's resume after the Christmas break. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "TonyWilliams via AccessMonster.com" <u56994(a)uwe> wrote in message news:a113a50afe1ef(a)uwe... > Yes Douglas I get the correct data for that company and that quarter so > the > sql is OK isn't it? > Tony > Incidently where are you, sorry to ask but I'm in the UK and it's 17.39 on > Xmas Eve and my wife is saying to me "Haven't you done enough?" > Douglas J. Steele wrote: >>Copy the SQL statement SELECT * FROM [tblmainIrish] WHERE ([txtmonthlabel] >>Between #2009-09-01# AND >>#2009-09-30#) AND [txtcompany] = 'AIB Commercial Services Ltd' to the >>clipboard. >> >>Create a new query, but don't select any tables for it. >> >>Switch the query to SQL view and paste the SQL statement into the SQL view >>window. >> >>Run the query. >> >>Do you get any rows returned? >> >>> This: >>> 24/12/2009 16:37:08 Loading Submenu from fLoadSubMenu() (debug logging >>[quoted text clipped - 20 lines] >>>>>> >>>>>>End Sub > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1 > |