Solved

embedding access query in VB

Posted on 2004-10-25
249 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
Question by:bushairkp
    1 Comment
     
    LVL 7

    Accepted Solution

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

     Java Android Coding Bundle

    Whether you're an Apple user or Android addict, learning to code for the Android platform is an extremely valuable, in-demand skill. It all starts with Java, the language behind the apps and games that make Android the top platform it is today.

    Suggested Solutions

    Title # Comments Views Activity
    notReplace  challenge 53 86
    wordsWithoutList  challenge 24 40
    Replace a tag with sed 2 17
    countAbc challenge 9 18
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now