Solved

SQL Statement

Posted on 2002-07-08
14
246 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
  • 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 50

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 50

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 50

Accepted Solution

by:
Ryan Chong earned 20 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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

770 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