Updating date from Excel VB

[Dr Zenith]

Guys,

I have an Access 2003 database with one of the column taking being Date/time data type. I am trying to edit the data in the database by taking in values from Excel through VBA (Am using ADODB connection). After editing it through the VB macro, all the modified values in database are changed to 1/0/1900 (which I understand to be the default date in Excel / Access).

I also tried editing the Format of the Date data-type to short date, but still the same thing.

Expecting some help on this!!

 

[boblarson]

So, is there any cleaner way to send date from excel to Access (other than sending it as text and storing it as date, there should be something better)

I don't know that there is, but I would put a breakpoint at the point just before the value is assigned to the variable and then F8 through the code and check to see that everything is working right. It might reveal something by doing that.

 

[Dr Zenith]

I don't know that there is, but I would put a breakpoint at the point just before the value is assigned to the variable and then F8 through the code and check to see that everything is working right. It might reveal something by doing that.

I have done that, and as mentioned before..right after declaring the variable the variable holds the value 12:00:00 AM till the assignment statement.

 

[Montez659]

It is being stored as date/text in the DB, so no issues.

Did you mean Date/Time as oppsoed to Date/Text? Just double checking.

 

[boblarson]

I have done that, and as mentioned before..right after declaring the variable the variable holds the value 12:00:00 AM till the assignment statement.

Yes, that is normal for a date variable. It would appear that the assignment isn't happening if the result is to return the 1900 date which you have (it is really 12/31/1899 but 1/0/1900 would be equivalent).

 

[Dr Zenith]

Did you mean Date/Time as oppsoed to Date/Text? Just double checking.

Oops, my bad!! it was date/time.

 

[xenou]

  If (Range(Cells(i, 23), Cells(i, 23)) = "Y") Or (Range(Cells(i, 23), Cells(i, 23)) = "y") Then
   
  num = Range(Cells(i, 1), Cells(i, 1)).Value
  val1 = Range(Cells(i, 5), Cells(i, 5)).Value
  If val1 = "" Then
  val1 = "Null"
  End If
  stSQL1 = " Update Table1 Set Priority =" & val1 & " Where S_Num = " & num & ""
  val2 = Range(Cells(i, 11), Cells(i, 11)).Value
  'If val2 = "" Then
  'val2 = "Null"
  'End If
  stSQL2 = " Update Table1 Set Day_One_Input =" & val2 & " Where S_Num = " & num & ""

What part of the above code is (supposed to be) picking up dates from Excel? What is in those cells in Excel?

Also very useful is to view the resulting SQL string:

  If (Range(Cells(i, 23), Cells(i, 23)) = "Y") Or (Range(Cells(i, 23), Cells(i, 23)) = "y") Then
   
  num = Range(Cells(i, 1), Cells(i, 1)).Value
  val1 = Range(Cells(i, 5), Cells(i, 5)).Value
  If val1 = "" Then
  val1 = "Null"
  End If
  stSQL1 = " Update Table1 Set Priority =" & val1 & " Where S_Num = " & num & ""
  val2 = Range(Cells(i, 11), Cells(i, 11)).Value
  'If val2 = "" Then
  'val2 = "Null"
  'End If
  stSQL2 = " Update Table1 Set Day_One_Input =" & val2 & " Where S_Num = " & num & ""   
  
[COLOR="Red"]   Debug.Print "stSQL1: " & stSQL1
   Debug.Print "stSQL2: " & stSQL2
[/COLOR]   'OR
[COLOR="Red"]   MsgBox "stSQL1: " & stSQL1
   MsgBox "stSQL2: " & stSQL2
[/COLOR]

It may be that you have just omitted hashes in dates. A typical string that uses a date (in Access raw SQL) uses hashes around dates:

INSERT INTO Table1 (Field1) Values ([COLOR="Red"]#[/COLOR]12/31/2011[COLOR="Red"]#[/COLOR]);

 

[boblarson]

A typical string that uses a date (in Access raw SQL) uses hashes around dates:

INSERT INTO Table1 (Field1) Values ([COLOR=red]#[/COLOR]12/31/2011[COLOR=red]#[/COLOR]);

And just an FYI - the technical term for it is an Octothorpe (#). (Just thought I would share that, in case you ever hear the term)

 

[xenou]

Indeed I've never heard the term before. My vocabulary has increased today!

 

[Dr Zenith]

It may be that you have just omitted hashes in dates. A typical string that uses a date (in Access raw SQL) uses hashes around dates:

INSERT INTO Table1 (Field1) Values ([COLOR=Red]#[/COLOR]12/31/2011[COLOR=Red]#[/COLOR]);

Bingo!! Octothorpe was what was missing in the query, it's working now. Thanks xenou!!

 

[xenou]

Great! What's an octothorpe?