Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Statement

Posted on 2002-07-08
14
Medium Priority
?
251 Views
Last Modified: 2010-05-02
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
Comment
Question by:aidanie
[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
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7139766
try this:

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

Hope this helps!

-priya



0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 7139812
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
 

Expert Comment

by:X14h
ID: 7139817
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
Independent Software Vendors: 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!

 
LVL 2

Expert Comment

by:priya_pbk
ID: 7139825
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
 

Author Comment

by:aidanie
ID: 7139845
Priya..

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

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

Expert Comment

by:priya_pbk
ID: 7139878

>>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
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7139916
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
 

Author Comment

by:aidanie
ID: 7139955
the msgbox"the OpenForm action was cancelled" appear when i run this code
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7139989
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
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 7140012
> the msgbox"the OpenForm action was cancelled" appear when i run this code

Bcos you pass the wrong parameters
0
 

Author Comment

by:aidanie
ID: 7140030
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
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 80 total points
ID: 7140056
TRy amend the code as:

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

regards
0
 

Author Comment

by:aidanie
ID: 7140064
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
 

Author Comment

by:aidanie
ID: 7140138
thanks..very much yeaa
0

Featured Post

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!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

604 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