Using Between in VBA?

[Walshie]

Hi,

I'm putting together a simple db report to list the top 10 items, in order, in the report.

I've got the code i need to calulate the spacing and listing etc. but i'm stuck with actually getting the correct data I need.

I have form with two boxes on it, txtFrom and txtTo, I want to input a date in each and the report list the top 10 transactions between this data range.

The code below that i'm using at the minute is working fine, until I put the "Between" into the sql. It basically ignores it!

Could someone let me know what i could do to get around this, i've tried basing the .openrecordsets onto a query i've created but it tells me there's too few arguements, expected 2, or that it cannot find the query.

Any help would be appreciated!

Here's the code:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Dim strContracts As String
    Dim intIndex     As Integer
   
strsql = "Select * FROM tblScrap WHERE  (((tblScrap.[transqty]) >0)) ORDER BY tblScrap.TransQty;"
 
    With CurrentDb.OpenRecordset(strsql)
        For a = 1 To 10
 
        intIndex = intIndex + 1
 
        If Len(a) > 1 Then
 
        If Len(![Item-no]) = 4 Then
        strContracts = strContracts & CStr(intIndex) & ".  " & ![Item-no] & Space((15 - Len(a) + 1.5) - Len(![Item-no])) & UCase(![TransQty]) & Space((15 - Len(a) + 1.5) - Len(![TransQty])) & UCase(![Description1]) & vbNewLine
        Else
         strContracts = strContracts & CStr(intIndex) & ".  " & ![Item-no] & Space((15 - Len(a) + 1.5) - Len(![Item-no]) - Len(a)) & UCase(![TransQty]) & Space((15 - Len(a) + 1.5) - Len(![TransQty])) & UCase(![Description1]) & vbNewLine
        End If
 
        Else
 
        If Len(![Item-no]) = 4 Then
        strContracts = strContracts & CStr(intIndex) & ".  " & ![Item-no] & Space((15 - Len(a) + 1.5) - Len(![Item-no])) & UCase(![TransQty]) & Space((14 - Len(a) + 1.5) - Len(![TransQty])) & UCase(![Description1]) & vbNewLine
        Else
        strContracts = strContracts & CStr(intIndex) & ".  " & ![Item-no] & Space((15 - Len(a) + 1.5) - Len(![Item-no]) - Len(a)) & UCase(![TransQty]) & Space((14 - Len(a) + 1.5) - Len(![TransQty])) & UCase(![Description1]) & vbNewLine
        End If
 
        End If
        .MoveNext
 
        Next
    End With
 
Me.txtTopAdjustments = strContracts
End Sub

 

[Trevor G]

Look to create a query that uses criteria to check the form txtbox and change the view of the query to make sure it shows the correct data then if you want that as SQL code you can change the view of the Query to SQL and copy the code into a Module sheet adapt it with

Dim strSQL As String

strSQL = The Code

 

[pr2-eugin]

Show us how you have used BETWEEN operator.. Is the above Query causing too few parameter error??

 

[Walshie]

Here's what i've been trying to do:

strsql = "Select * FROM tblScrap WHERE  (((tblScrap.[trans-date] between " & Format(Forms!frmMain!txtFrom.Value, "######") & " and " & Format(Forms!frmMain!txtTo.Value, "######") & ")) and ((tblScrap.[reason code]) ='scrp')) ORDER BY tblScrap.TransQty;"
 
    With CurrentDb.OpenRecordset(strsql)

I get the error "No record set" suggesting no records match the criteria, even though i know they do.

Hope you can see what i'm trying to do

Cheers
Chris

 

[pr2-eugin]

Could you please answer the following for me please..

* Is the field [trans-date] is a Date/Time Format on the table?

* If not why are you using a Format? Does the field hold a String which has formatted text in it?

* Could you give an example of the data that resides in [trans-date]??

 

[Walshie]

Thanks for the help!

[trans-date] is a date

and example of a value in the above field is "03/09/2012" I've also tried the above SQL without the "format(......)"

Chris

 

[Walshie]

I've even pulled the SQL out of a working query and added this in, I still get the same error with the following code:

strsql = "Select * FROM tblScrap WHERE  (((tblScrap.[Reason Code])='scrp') AND ((tblScrap.[trans-date]) Between " & [Forms]![frmMain]![txtFrom] & " And " & [Forms]![frmMain]![txtTo] & "));"
 
 With CurrentDb.OpenRecordset(strsql)

 

[pr2-eugin]

To refer dates, you need to use the #.. so try the following code..

strsql = "SELECT * FROM tblScrap WHERE (((tblScrap.[trans-date] BETWEEN # " & Forms!frmMain!txtFrom.Value & " # AND # " & Forms!frmMain!txtTo.Value & " # AND ((tblScrap.[reason code]) ='scrp')) ORDER BY tblScrap.TransQty;"

 

[Walshie]

So simple when you know how!

Thanks this worked perfect!

Cheers
Chris

 

[pr2-eugin]

You are most welcome..
Just to keep it simple..
* String needs to be surrounded by single quotes e.g. strCriteria = 'A String'
* Dates by # e.g. dtCriteris = #3/16/2012#
* Integers on its own e.g intCriteria = 12