Re: Return ID value of inserted record, and populate text box

In case any user of the database has his/her Short Date format set to
dd/mm/yyyy, you're better off using

"AND s.stu_birthday = " & Format(Me.txtDOB.Value, "\#yyyy\-mm\-dd\#")

(note that the semi-colon at the end is optional)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"gsnidow via AccessMonster.com" <u33475@uwe> wrote in message
news:9a7d96ae04cc8@xxxxxx
Ok, not used to querying in Access. I got it to work by changing single
quotes to #...

"AND s.stu_birthday = " & "#" & Me.txtDOB.Value & "#" & ";"

Anyhow, it works, thanks for the help Doug.

gsnidow wrote:
Ok, the problem is that I am trying to compare STUDENTS.stu_birthday,
which
is a date field, to txtDOB, which is text. So, now my problem is, how to
I
convert this line...

'"AND s.stu_birthday = '" & Me.txtDOB.Value & "';"

In TSQL I would want 'AND s.stu_birthday =
CONVERT(SMALLDATETIME,me.txtDOB,
101)'

Can you tell me how to do this with VB? Thanks for the help.

Thanks for the quick reply. Well, now I am getting a different error,
"Data
type mismatch in criteria expression", so I guess that is progress.
Could
[quoted text clipped - 6 lines]
Me.txtStudent_ID = (rsSTUDENT_ID("STUDENT_ID"))
Me.txtUser.Requery

--
Message posted via http://www.accessmonster.com



.

Relevant Pages

  • Re: dlookup
    ... Doug Steele, Microsoft Access MVP ... Date format set to dd/mm/yyyy, then that's what's going to be presented ... treat it as mm/dd/yyyy in the DLookup statement. ...
    (microsoft.public.access.formscoding)
  • Re: Date format error
    ... you cannot format the date as dd/mm/yyyy and expect it to work reliably. ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: Select Where Two Condiotions as met
    ... The problem with the original statement was the placement of quotes (plus ... some wonky parentheses). ... Doug Steele, Microsoft Access MVP ... Note that the SQL statement ends with semi-colon. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Sql Statement errors
    ... Actually, the semi-colon is always optional in Access queries, so you can ... Doug Steele, Microsoft Access MVP ... (no e-mails, please!) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Delete query
    ... The semi-colon is optional, so there's little chance that's the problem. ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ...
    (microsoft.public.access.gettingstarted)