Solved

SQL Statement

Posted on 2002-07-08
14
247 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

809 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