• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

SQL Statement

I have SQL Statement like this :

SELECT Open_Date, Due_Date
FROM Tender
Where Open_Date BETWEEN Text2 AND Due_Date Text3;

How do I declare it in VB and trasform the code in VB?

User will key in value open_date in text2 while value due_date in text3.
0
aidanie
Asked:
aidanie
  • 5
  • 5
  • 3
  • +1
1 Solution
 
priya_pbkCommented:
try this:

strSql="SELECT Open_Date, Due_Date FROM Tender " & _
" Where Open_Date BETWEEN '" & Text2 & "' AND '" & Text3 & "'"

Hope this helps!

-priya



0
 
Ryan ChongCommented:
If in M$ Access, try use "#" instead of "'":

strSql="SELECT Open_Date, Due_Date FROM Tender " & _
" Where Open_Date BETWEEN #" & format$(Text2,"mm/dd/yyyy") & "# AND #" & format$(Text3,"mm/dd/yyyy") & "#"

Use format function to ensure the date passed is in actual date.
0
 
X14hCommented:
i would convert the text, explicitly , to a date, like this:

'Checking if we have dates !
if not isdate(text2) then
  msgbox "Text2 not a date !"
  exit sub
endif
if not isdate(text3) then
  msgbox "Text3 not a date !"
  exit sub
endif

'Creating the SQL statement
strSql = "SELECT Open_Date, Due_Date " & _
         "FROM Tender " & _
         "WHERE Open_Date BETWEEN " & CDate(Text2) & _
               " AND " & CDate(Text3) & ";"



Regards,
X14h
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
priya_pbkCommented:
yes that's right, format it and use "#" in Access and " ' " in sql

Also i feel the date format can be "dd-mmm-yyy" and not "dd-mm-yy", since the latter would still be confusing.

Therefore the changes would be:


strSql="SELECT Open_Date, Due_Date FROM Tender " & _
" Where Open_Date BETWEEN '" & format(Text2,"dd/mmm/yyyy") & "' AND '" & format(Text3,"dd/mmm/yyyy") & "'"


I think this is what you require!

-priya



0
 
aidanieAuthor Commented:
Priya..

Can u check this syntax(For 1 line code only) :

stLinkCriteria = "[OPEN DATE] BETWEEN'" & Me![Text2] & "'And "[DUE DATE]'" & "'" & Me![Text3] & "'"
0
 
priya_pbkCommented:

>>stLinkCriteria = "[OPEN DATE] BETWEEN'" & Me![Text2] & "'And "[DUE DATE]'" & "'" & Me![Text3] & "'"

what is the "[DUE DATE]'" doing here.

It is very simple aidanie. What has to be done is you need to retreive all the Open_date and Due_date from the tender table which falls between 2 dates

for eg:if you want all the records dated say today 9th July 02, your query would be something like this, in simple english:

strsql="Select [Open Date],[Due Date] from Tender where [Open Date] between '9-Jul-02' and '10-Jul-02'

This will give you all the records which has Open date as 9th jul 02



So when you put it in VB it becomes...

strSql="SELECT Open_Date, Due_Date FROM Tender " & _
" Where Open_Date BETWEEN '" & format(Text2,"dd/mmm/yyyy") & "' AND '" & format(Text3,"dd/mmm/yyyy") & "'"


---------------------------------------------
With respect to your recent comment,

stLinkCriteria = "[OPEN DATE] BETWEEN'" & format(Text2,"dd/mmm/yyyy") & "'And " & "'" & format(Text3,"dd-mmm-yyyy") & "'"

Got it!

-priya

0
 
priya_pbkCommented:
slight change,that would be:

stLinkCriteria = "[OPEN DATE] BETWEEN'" & format(Text2,"dd/mmm/yyyy") & "' And '" & format(Text3,"dd-mmm-yyyy") & "'"

unnecessary concatenation after "AND", not required but would have worked. hope you are getting this right!

-priya
0
 
aidanieAuthor Commented:
the msgbox"the OpenForm action was cancelled" appear when i run this code
0
 
priya_pbkCommented:
do write in your code, and let us know what you are trying to accomplish, and on which line is the error being genrated. This would point EE in right direction!

Are you using Access, coz the "OpenForm" syntax comes with Access, if i remember it right. This will only come when you are trying to open a form and I think this is no way  related to the sql query.

-priya
0
 
Ryan ChongCommented:
> the msgbox"the OpenForm action was cancelled" appear when i run this code

Bcos you pass the wrong parameters
0
 
aidanieAuthor Commented:
YES I'M USING ACCESS. THIS ARE THE CODES.

Private Sub cmdSEARCH1_Click()
On Error GoTo Err_cmdSEARCH1_Click
   
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "TENDER"
   
stLinkCriteria = "[OPEN DATE] BETWEEN'" & (Me.Text2) & "' And '" & (Me.Text3) & "'"
     
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.CLOSE
   
Exit_cmdSEARCH1_Click:
    Exit Sub

Err_cmdSEARCH1_Click:
    MsgBox Err.Description
    Resume Exit_cmdSEARCH1_Click

End Sub
0
 
Ryan ChongCommented:
TRy amend the code as:

stLinkCriteria = "[OPEN DATE] BETWEEN #" & format$(Me.Text2,"mm/dd/yyyy") & "# And #" & format$(Me.Text3,"mm/dd/yyyy") & "#"

regards
0
 
aidanieAuthor Commented:
YES I'M USING ACCESS. THIS ARE THE CODES.

Private Sub cmdSEARCH1_Click()
On Error GoTo Err_cmdSEARCH1_Click
   
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "TENDER"
   
stLinkCriteria = "[OPEN DATE] BETWEEN'" & (Me.Text2) & "' And '" & (Me.Text3) & "'"
     
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.CLOSE
   
Exit_cmdSEARCH1_Click:
    Exit Sub

Err_cmdSEARCH1_Click:
    MsgBox Err.Description
    Resume Exit_cmdSEARCH1_Click

End Sub
0
 
aidanieAuthor Commented:
thanks..very much yeaa
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now