Solved

SQL Statement

Posted on 2002-07-08
14
244 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 49

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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 49

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba find the last empty column 10 88
ms access #TYPE! error on report when no data 4 63
bit defender blocks good applications 2 80
Advice in Xamarin 21 48
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

919 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

17 Experts available now in Live!

Get 1:1 Help Now