[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

embedding access query in VB

Posted on 2004-10-25
1
Medium Priority
?
299 Views
Last Modified: 2010-04-17
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
Comment
Question by:bushairkp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 7

Accepted Solution

by:
jacobhoover earned 150 total points
ID: 12404056
Try:
Sql = "(SELECT MAX(EFFDT)  FROM SALARY WHERE  EMPLID='" & Label2.Caption & "' AND EFFDT= #" & Format(cdate(datep),"mm/dd/yyyy") & "# ) " 
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question