Storing dates

[Ana]

Hi,

In a form I have a Yes/No bit which when checked, stores a 1 in my SQL
table. I need to store today's date as well in a field called today_date
when the bit is set but in an yyyymmdd format. How do I accomplish it?

TIA

Ana



--------------------------------------------------------------------------------

Estoy utilizando la versión gratuita de SPAMfighter para usuarios privados.
Ha eliminado 390 correos spam hasta la fecha.
Los abonados no tienen este mensaje en sus correos.
¡Pruebe SPAMfighter gratis ya!

 

[Douglas J. Steele]

You're better off just storing the date in a Date field, and applying a
format to it.

If you must use a text field, you can use Format(Date(), "yyyymmdd") to get
today's date in that format.

 

[Ana]

Thank you for answering.

Unfortunately cannot change the table's structure.

But how would I populate a field today_date with Format(Date(), "yyyymmdd")
when the yes/no bit is selected?Thank you again

 

[Douglas J. Steele]

Easiest way would likely be to put logic in the AfterUpdate event of
checkbox to set the today_date field.

Private Sub MyCheckBox_AfterUpdate()

If Me.MyCheckBox = True Then
Me.today_date = Format(Date(), "yyyymmdd'0
Else
Me.today_date = vbNullString
End If

End If

 

[Ana]

Great, it worked. The only problem I see is that the check is stored as a -1
instead of 1 in the SQL table.

 

[Douglas J. Steele]

Sorry, I can't remember whether Access takes care of that, or if you have to
do something.

 

[Rick Brandt]

Sorry, I can't remember whether Access takes care of that, or if you
have to do something.

Are you looking at it directly in SQL Server tools or in the link? A bit
field in an Access link will display -1 even though 1 is what is stored in
the table.

 

[Ana]

I'm looking it in the link and it's shown as -1 but, interesting enough,
it's shown as null in the server. I guess I need to investigate a bit more.

 

[Rick Brandt]

I'm looking it in the link and it's shown as -1 but, interesting enough, it's
shown as null in the server. I guess I need to investigate a bit more.

If you need to interact with SQL Server bit fields from Access you don't want
the table to allow nulls in that field. Lots of problems with that (yes/no in
Access doesn't support null). Personally I avoid bits and just use Integers.
Then -1 is really stored as -1.