VB & Database, Access date/time null values

Access date/time null values

Author	Message
PAJ #1 / 7	Access date/time null values Hi I have a bit of a tricky one! I have 2 fields in an Access 2000 database, CloseDate and CloseTime that have their properties set to shortdate and longtime respectively. My app fills in these values without any problem. However the problem comes when on the rare occasion I will have to re-open these closed fields and set the date back to blank. I can do this by opening Access and manually deleting the record but I can find no way of doing it from my app. I Have tried setting the corresponding text boxes to "" and " " but this just generated an error "Data type mismatch in expression". Does anyone know of a way to do this from VB or will I just have to do this manually? I could set the fields back to plain text but this would mess up my reports when I have to produce reports based on dates/times. Does anyone have any ideas? thx. -- PJ
Thu, 06 Jan 2005 02:12:48 GMT

Adam D. Barrat #2 / 7	Access date/time null values [...] Quote: > I have 2 fields in an Access 2000 database, CloseDate and CloseTime > that have their properties set to shortdate and longtime respectively. [...] > However the problem comes when on the rare occasion I will have to > re-open these closed fields and set the date back to blank. [...] > I Have tried setting the corresponding text boxes to "" and " " but > this just generated an error "Data type mismatch in expression". You've given yourself the answer in the subject line. Rather than attempting to set the fields value to " " (how on earth is that supposed to be a valid date?) or "" (it's a date, not a string), set them to NULL. The whole purpose of NULL in database fields is to indiciate the abscense of a value. Even if you were dealing with a string field, "" <> NULL. The former indicates a value that is empty, the latter that no value is present. hth Adam -- I'm glad I was not born before tea. -- Sidney Smith (1771-1845)
Thu, 06 Jan 2005 02:38:05 GMT

PAJ #3 / 7	Access date/time null values On 20 Jul 2002 19:38:05 +0100, "Adam D. Barratt" Quote: >[...] >> I have 2 fields in an Access 2000 database, CloseDate and CloseTime >> that have their properties set to shortdate and longtime respectively. >[...] >> However the problem comes when on the rare occasion I will have to >> re-open these closed fields and set the date back to blank. >[...] >> I Have tried setting the corresponding text boxes to "" and " " but >> this just generated an error "Data type mismatch in expression". >You've given yourself the answer in the subject line. Rather than >attempting to set the fields value to " " (how on earth is that supposed >to be a valid date?) or "" (it's a date, not a string), set them to >NULL. The whole purpose of NULL in database fields is to indiciate the >abscense of a value. >Even if you were dealing with a string field, "" <> NULL. The former >indicates a value that is empty, the latter that no value is present. >hth >Adam Hi Thanks for the reply. Sorry I am new to VB and access. Are you saying it is possible? If so how do I set it to Null? I have the following query string: strSQL = "UPDATE calls SET [Status]='" strSQL = strSQL & txtStatus.Text strSQL = strSQL & "', [CloseDate]='" strSQL = strSQL & txtCloseDate.Text strSQL = strSQL & "', [CloseTime]='" strSQL = strSQL & txtCloseTime.Text strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text) Thanks for any help. -- PJ
Thu, 06 Jan 2005 04:27:32 GMT

Adam D. Barrat #4 / 7	Access date/time null values Quote: > On 20 Jul 2002 19:38:05 +0100, "Adam D. Barratt" [...] >>You've given yourself the answer in the subject line. Rather than >>attempting to set the fields value to " " (how on earth is that supposed >>to be a valid date?) or "" (it's a date, not a string), set them to >>NULL. The whole purpose of NULL in database fields is to indiciate the >>abscense of a value. [...] > strSQL = strSQL & "', [CloseDate]='" > strSQL = strSQL & txtCloseDate.Text > strSQL = strSQL & "', [CloseTime]='" > strSQL = strSQL & txtCloseTime.Text Replace with: strSQL = strSQL & "', CloseDate=" & IIf( Trim$( txtCloseDate.Text ) _ = "", "NULL", "'" & txtCloseDate.Text & "'" ) & ", CloseTime = " & _ IIf( Trim$( txtCloseTime.Text ) = "", "NULL", "'" & _ txtCloseTime.Text & "'") In each case, if the textbox is empty, NULL is passed, otherwise the contents of the textbox are passed surrounded by single quotes. Quote: > strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text) ^ ^^^^^ ^ Remove the closing single quote there, as it's been dealt with by the previous line. Remove the CInt() as it's redundant - you're taking a string, and concatenating it to a second string; converting to and from an Integer in the process is pointless and unneccessary. Wherever possible, avoid multiple string concatentations (repeated strSQL = strSQL &... in your example). They're slower and more expensive than simply continuing the assignment over multiple lines, as in my rework above. I'd also suggest the acquisition of a good database theory book, together with a VB book. None of the above is particularly complicated, although it may seem that way to a novice. hth Adam -- "Do not go where the path may lead, go instead where there is no path and leave a trail." -- Ralph Waldo Emerson (1803-1882)
Thu, 06 Jan 2005 05:01:16 GMT

PAJ #5 / 7	Access date/time null values On 20 Jul 2002 22:01:16 +0100, "Adam D. Barratt" Quote: >> On 20 Jul 2002 19:38:05 +0100, "Adam D. Barratt" >[...] >>>You've given yourself the answer in the subject line. Rather than >>>attempting to set the fields value to " " (how on earth is that supposed >>>to be a valid date?) or "" (it's a date, not a string), set them to >>>NULL. The whole purpose of NULL in database fields is to indiciate the >>>abscense of a value. >[...] >> strSQL = strSQL & "', [CloseDate]='" >> strSQL = strSQL & txtCloseDate.Text >> strSQL = strSQL & "', [CloseTime]='" >> strSQL = strSQL & txtCloseTime.Text >Replace with: > strSQL = strSQL & "', CloseDate=" & IIf( Trim$( txtCloseDate.Text ) _ > = "", "NULL", "'" & txtCloseDate.Text & "'" ) & ", CloseTime = " & _ > IIf( Trim$( txtCloseTime.Text ) = "", "NULL", "'" & _ > txtCloseTime.Text & "'") >In each case, if the textbox is empty, NULL is passed, otherwise the >contents of the textbox are passed surrounded by single quotes. >> strSQL = strSQL & "' WHERE [callid]=" & CInt(txtSearchCallID.Text) > ^ ^^^^^ ^ >Remove the closing single quote there, as it's been dealt with by the >previous line. Remove the CInt() as it's redundant - you're taking a >string, and concatenating it to a second string; converting to and from >an Integer in the process is pointless and unneccessary. >Wherever possible, avoid multiple string concatentations (repeated >strSQL = strSQL &... in your example). They're slower and more expensive >than simply continuing the assignment over multiple lines, as in my >rework above. >I'd also suggest the acquisition of a good database theory book, >together with a VB book. None of the above is particularly complicated, >although it may seem that way to a novice. >hth >Adam Hi Adam Brilliant! thanks very much :) That did it and I will take the advice about the books. thx -- Paj
Thu, 06 Jan 2005 06:00:07 GMT

Modiglian #6 / 7	Access date/time null values SQL = "UPDATE MyTable SET CloseDate = NULL, CloseTime = NULL WHERE ( .... ); BTW, there's no need to have two fields for this. Date values include the time. ShortDate and LongTime are display formats, that have nothing to do with the field's data format. Quote: > Hi > I have a bit of a tricky one! > I have 2 fields in an Access 2000 database, CloseDate and CloseTime > that have their properties set to shortdate and longtime respectively. > My app fills in these values without any problem. > However the problem comes when on the rare occasion I will have to > re-open these closed fields and set the date back to blank. > I can do this by opening Access and manually deleting the record but I > can find no way of doing it from my app. > I Have tried setting the corresponding text boxes to "" and " " but > this just generated an error "Data type mismatch in expression". > Does anyone know of a way to do this from VB or will I just have to do > this manually? > I could set the fields back to plain text but this would mess up my > reports when I have to produce reports based on dates/times. > Does anyone have any ideas? > thx. > -- > PJ
Thu, 06 Jan 2005 08:29:07 GMT

Douglas J. Steel #7 / 7	Access date/time null values You've already got advise on how to use Null. I'd just like to comment that it's usually MUCH better to have a single date/time field than storing them as two separate fields. For those occasions where you need only the date portion, or only the time portion, you can use the DateValue and TimeValue functions. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele Quote: > Hi > I have a bit of a tricky one! > I have 2 fields in an Access 2000 database, CloseDate and CloseTime > that have their properties set to shortdate and longtime respectively. > My app fills in these values without any problem. > However the problem comes when on the rare occasion I will have to > re-open these closed fields and set the date back to blank. > I can do this by opening Access and manually deleting the record but I > can find no way of doing it from my app. > I Have tried setting the corresponding text boxes to "" and " " but > this just generated an error "Data type mismatch in expression". > Does anyone know of a way to do this from VB or will I just have to do > this manually? > I could set the fields back to plain text but this would mess up my > reports when I have to produce reports based on dates/times. > Does anyone have any ideas? > thx. > -- > PJ
Thu, 06 Jan 2005 19:44:02 GMT

Page 1 of 1

[ 7 post ]