By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Sql Statement Syntax

P: n/a
Access 2000 Frt End connected to SQL 2000

I am trying to ck for the existance of a record in another table with
the same ClientID and Date.
I am ck'ing for a record in tblMedInfo that has the same "ClientID"
(Long Interger) and "DateEntered" (Short Date mm/dd/yyyy) as the form
"frmClients.ClientID" and "frmClients.LastUpdate"

Yet again, it's the sql statement that has me stumped.

Dim dbs As Database
Dim rst As Recordset
Dim qry As String
Dim vDate As Date

vDate = Me.LastUpdate

Set dbs = CurrentDb()
qry = "Select * from tblMedInfo where tblMedInfo.[ClientID] = " &
Forms!frmClients.[ClientID] & "" And (((tblMedInfo.DateEntered) = " & #
vDate # & "))

Set rst = dbs.OpenRecordset(qry, dbOpenDynaset)
If rst.RecordCount 0 Then
MsgBox "Record Exsist"
Else
MsgBox "Will Open And Add Record"
End If

Any Suggestions Appreciated
Dale

Oct 11 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
pks
Dale,

Are you receiving an error message, or is it just not bringing up the
record? WIthout being able to test your code right now, I can tell you
that the recordcount isn't populated until you do a rst.MoveLast.

Another option is to change your statement to
If rst.eof = false then
...
Else

Oct 11 '06 #2

P: n/a
pks
Dale,

Are you receiving an error message, or is it just not acknowledging the
record? Without being able to test your code right now, I can tell you
that the recordcount isn't populated until you do a rst.MoveLast.

Another option is to use EOF (end of file) in your statement, such as:
If rst.eof = false then
...
Else
...
End If

pks

Oct 11 '06 #3

P: n/a
Bri


pks wrote:
Dale,

Are you receiving an error message, or is it just not acknowledging the
record? Without being able to test your code right now, I can tell you
that the recordcount isn't populated until you do a rst.MoveLast.

Another option is to use EOF (end of file) in your statement, such as:
If rst.eof = false then
...
Else
...
End If

pks
If there are records then RecordCount will be 1 (one) prior to a Move,
so his IF statement will work if the query is working as he is just
testing that it is greater than zero.

--
Bri

Oct 11 '06 #4

P: n/a
Bri

Dale wrote:
Access 2000 Frt End connected to SQL 2000

I am trying to ck for the existance of a record in another table with
the same ClientID and Date.
I am ck'ing for a record in tblMedInfo that has the same "ClientID"
(Long Interger) and "DateEntered" (Short Date mm/dd/yyyy) as the form
"frmClients.ClientID" and "frmClients.LastUpdate"

Yet again, it's the sql statement that has me stumped.

Dim dbs As Database
Dim rst As Recordset
Dim qry As String
Dim vDate As Date

vDate = Me.LastUpdate

Set dbs = CurrentDb()
qry = "Select * from tblMedInfo where tblMedInfo.[ClientID] = " &
Forms!frmClients.[ClientID] & "" And (((tblMedInfo.DateEntered) = " & #
vDate # & "))

Set rst = dbs.OpenRecordset(qry, dbOpenDynaset)
If rst.RecordCount 0 Then
MsgBox "Record Exsist"
Else
MsgBox "Will Open And Add Record"
End If

Any Suggestions Appreciated
Dale
You have an extra " and some missing ones. The # symbols need to be in
the quoted part. It is a good idea to declare the table that the * is
referring too (for your benifit and it is required if you want to view
this in the Query Designer). Also, to avoid Date confusions it is a good
idea to format it into an unambiguous form before inserting it into a
string. I am assuming that ClientID is a number and not text. I cleaned
up your statement below:

qry = "Select tblMedInfo.* from tblMedInfo " & _
"Where tblMedInfo.ClientID = " Forms!frmClients.ClientID & _
" And tblMedInfo.DateEntered = #" & Format(vDate,"dd-mmm-yy") & "#"

Give that a try and if you still have problems come back with more info.

--
Bri

Oct 11 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.