Format(Date,"mm/dd/yyyy") = 12/30/1899

[GraeagleBill]

Format(Date,"mm/dd/yyyy") = 12/30/1899
Silly me, I was expecting 03/28/2022

Same thing if I
Dim dteDate as date
dteDate =Date()


msgbox format(dtedate, "mm/dd/yyyy")
12/30/1899

It's been a long day, but this one's "off the charts.

Maybe the clue is here:

strSql = "INSERT INTO Groupings(RegistryID,GroupID,GroupPhoneBroadcast,DateAdded)"
strSql = strSql & " VALUES(" & numRegID & ", " & numGrpID & ", " & bolAutoBroadcast & ", " & Format(Date, "mm/dd/yyyy") & ");"

"DateAdded" field in table Groupings is Date/Time. It's format is mm/dd/yyyy

[June7]

Well, I don't know why your VBA is wrong but the SQL statement needs # delimiters for the date input.

[GraeagleBill]

Thanks, that did the trick.......... I should have known that one (I couldn't re-produce the 1899, so have no idea how I managed that one?)

strSql = "INSERT INTO Groupings(RegistryID,GroupID,GroupPhoneBroadcast,DateAdded)"
strSql = strSql & " VALUES(" & numRegID & ", " & numGrpID & ", " & bolAutoBroadcast & ", #" & Format(Date, "mm/dd/yyyy") & "#);"

[June7]

On reflection, why do you even use Format function? Consider:

& bolAutoBroadcast & ", Date());"

[isladogs]

In Access, days are counted from a baseline date of 12/30/1899. So that output indicates that the expression had no data. Null isn't allowed for numbers so was resolved as value zero

[GraeagleBill]

Ah! I've always thought the epoch was 01/01/1900, so it didn't occur to me what 12/30/1899 might be.

[GraeagleBill]

That's how I started, but the default date format excludes leading zeros. The only way I could get the table to update with leading zeros was to stipulate the format mm/dd/yyyy in the field definition in the table design itself. But yes, having done that, your suggestion works fine.

[Welshgasman]

No one mentioned reserved word?

[isladogs]

Where has a reserved word been used? Its the Date function not a field called Date.

[June7]

Using Format function or format property in table has nothing to do with how value is stored in Date/Time field. If you want date displayed with placeholder zeroes, set format in textbox. Advise not to do any data formatting in table.

[GraeagleBill]

I will heed your caution, but why would that cause any problems?

[Welshgasman]

Where has a reserved word been used? Its the Date function not a field called Date.

How can the real Date variable not have a value?

[isladogs]

How can the real Date variable not have a value?

No idea. Bill said it wasn't reproducible

[June7]

Setting formatting can obscure what is actually stored in field. A poster provided a database that formatted a date/time field to MMM-yyyy, couldn't see the day. Found it annoying and had to remove. If a date/time field has data with time components, setting format to MM/DD/YYYY can cause frustration when filtering doesn't perform as expected and the cause is not obvious because time components are not displayed.

[GraeagleBill]

Ah yes! I see what you mean. Not many of my apps have time displays, but I can see from your point where I could find more trouble than I'd like.
Thanks,
Bill