Sql Statement Syntax

[Dale]

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 "DateEntere d" (Short Date mm/dd/yyyy) as the form
"frmClients.Cli entID" and "frmClients.Las tUpdate"

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!frmClient s.[ClientID] & "" And (((tblMedInfo.D ateEntered) = " & #
vDate # & "))

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

Any Suggestions Appreciated
Dale

[pks]

Re: Sql Statement Syntax

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

[pks]

Re: Sql Statement Syntax

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

[Bri]

Re: Sql Statement Syntax



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

[Bri]

Re: Sql Statement Syntax


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 "DateEntere d" (Short Date mm/dd/yyyy) as the form
"frmClients.Cli entID" and "frmClients.Las tUpdate"
>
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!frmClient s.[ClientID] & "" And (((tblMedInfo.D ateEntered) = " & #
vDate # & "))
>
Set rst = dbs.OpenRecords et(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.Clie ntID = " Forms!frmClient s.ClientID & _
" And tblMedInfo.Date Entered = #" & Format(vDate,"d d-mmm-yy") & "#"

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

--
Bri