2

Im a little stuck and I dont know what im doing wrong. I want to insert the current date into a MS Access database in VB. The part of the code im having trouble with is:

SQLString = "INSERT INTO Sales(DateSold) VALUES ("

SQLString += Date.Today & ")"

The type in the database is date and is dd/mm/year. Thanks in advance for the help.

Improve this question
1
  • If you always want today's date, you might consider setting the default value in the mdb to Date, and then it would not need to be inserted. Commented Aug 20, 2009 at 12:05

3 Answers 3

Reset to default
2

If you didn't what to use the SQL NOW() you could wrap the date in quotes:

SQLString = "INSERT INTO (Sales) VALUES ("

SQLString += "'" & Date.Today & "')"
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The quotes are for strings though? It needs to be exact for the db to take the info.
In all version of Access I've used it will accept dates, into date columns, using single quotes. Just as SQL Server does The only difference is in Access VBA where you can use # (hash) insted of single quote. As Tikeb says - you'd also benefit from using cultureInfo to make use the data is in a format that Access will interpret correctly (either ISO YYYYMMDD or American MMDDYYYY).
Ah ill give it a try, I just thought that dates wouldnt use quotes.
The difference is what data access method you're using. Via DAO, you use the # delimiter. In Access directly, you'll use # if you're using the default "SQL 89" querying mode, and ' if using "SQL 92." If you're using ADO, you'll use '. In VBA, you'll use the one appropriate to the data interface layer you're using, i.e., DAO or ADO. From outside Access, again, if ADO, ', and if DAO, #.
Thanks David, I have vague recollections about the # thing being DAO only. But clarifying that DAO uses SQL 89 (and hence #) helps. Cheers :)
0

why don't you let access handle the date:

"INSERT INTO Sales VALUES (NOW())"

Improve this answer

2 Comments

I tried that but it doesnt like the format thats been entered into the db then. I think it might be trying to add the time and the date with that command.
If you're letting "Access" handle it, and you want a data, you'd use Date() instead of Now(), as Now() returns both date and time. But from outside of Access itself, neither of those functions will work reliably. Secondly, you're depending on it getting optimized properly, i.e., that it won't execute for every row. Resolving the date value before passing it to the processing database engine guarantees it won't be mis-optimized.
0

You could use CultureInfo to set the formats of your datetimes?

Improve this answer

1 Comment

We use CultureInfo to make sure the dates are formatted en-GB : dd/mm/yyyy instead of US mm/dd/yyyy. Was just thinking maybe that could be the problem, if not aww well..

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.