Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

embedding access query in VB

Hi guys

  i am using vb and ms access.
see the following code
SELECT MAX(EFFDT)  FROM SALARY WHERE  EMPLID='1' AND EFFDT<= #01/01/2000#

its working properly as an access query
also the following one ...

(SELECT MAX(EFFDT)  FROM SALARY WHERE  EMPLID='1' AND EFFDT<=cDate(01/01/2000))

but i am facing problem when i am trying to add this code to Visual Basic as shown below (i want to pass the value of "datep" instead of using "#01/01/2000#" ....so i used the following code

Sql = "(SELECT MAX(EFFDT)  FROM SALARY WHERE  EMPLID='" & Label2.Caption & "' AND EFFDT= '" & datep & "' ) " 
MsgBox Sql
Set rstGetRecord = New Recordset
With rstGetRecord
    .ActiveConnection = conn
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
End With
rstGetRecord.Open Sql

its not working working

also i used the following sql
Sql = "(SELECT MAX(EFFDT)  FROM SALARY WHERE  EMPLID='" & Label2.Caption & "' AND EFFDT= #" & datep & "# ) "

 the second sql is returning an error named as "syntax error in date in query expression 'EMPLID='1' AND EFFDT=#01:01:2000#

ALSO I USED ANOTHER SQL AS SHOWN BELOW

Sql = "(SELECT MAX(EFFDT)  FROM SALARY WHERE  EMPLID='" & Label2.Caption & "' AND EFFDT= " & cdate(datep) & " ) " 


ITS Returning an error "syntax error in date in query expression 'EMPLID='1' AND EFFDT=01:01:2000"


in the above example label2.caption stands for EMPLID and datep is the date supplied. i am getting an  error as "Data type Mismatch in criteria expression"

please help me...How can i resolve this problem...
thanks in advance


0
bushairkp
Asked:
bushairkp
1 Solution
 
jacobhooverCommented:
Try:
Sql = "(SELECT MAX(EFFDT)  FROM SALARY WHERE  EMPLID='" & Label2.Caption & "' AND EFFDT= #" & Format(cdate(datep),"mm/dd/yyyy") & "# ) " 
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now