Records Between 2 fields

G

Guest

Tran date Total Tran Tran ID Merch Name Bank Name Term ID
0710 22 247 EBS Center El Nilein Indstrial Development Bank G 12345678
0711 63 262 EBS Center El Nilein Indstrial Development Bank G 12345678
0714 5 538 Bank El Mozaree POS El Nilein Indstrial Development Bank
G 12345678
0715 2 599 Bank El Mozaree POS Farmers Commerical Bank 22111000
0717 7 617 EBS Center El Nilein Indstrial Development Bank G 12345678
0718 7 620 EBS Center El Nilein Indstrial Development Bank G 12345678
0721 3 645 Masraf El Mozare3 Pharmacy Farmers Commerical Bank 22111000

This How my table look like I inherit this table with date column VarChar(50).
What I would like to do is to get total Tran Id Between 2 dates let say 0710
and 0715
I did a code in access to get me all records between 2 dates by creating a
form with 2 combo box pointing to the date column and a button with the
following command

Private Sub Command4_Click()
'Table: have Bank name VarChar (50), Bank transactions, Transactions
date VarChar (50), Total Transactions (for all banks)
'Required: filter by Bank name and/or transaction between 2 dates (Date
column type is VarChar (50)).
'Able to filter by Bank name.
'Using Access.
'Have 2 combo Box Start date (Text1), End date (Tesxt2).
Dim StrSql As String, StrgSql As String, IntCounter As Integer, ResSub As
Integer
StrSql = Text1
StrgSql = Text2
If Me("Text1") <> "" Then
If Me("Text2") <> "" Then
ResSub = StrgSql - StrSql
End If
End If
'Set Filter for ResSub positive value
If ResSub > 0 Then
For IntCounter = 1 To ResSub
StrSql = StrSql & "[" & Me("Text1") + IntCounter & "]" &
Me("Text1") + IntCounter & Chr$(34) & " And"
Next

'Strip Last " And "
If StrSql <> "" Then
StrSql = Left(StrSql, (Len(StrSql) - 5))
'Set Filter Property
DoCmd.OpenReport "RepBankTotalTran", A_PREVIEW 'Open report.
Reports![RepBankTotalTran].Filter = StrSql
Reports![RepBankTotalTran].FilterOn = True
End If
Else
'Set Filter for ResSub negative value
If ResSub < 0 Then
ResSub = -1 * ResSub
For IntCounter = 1 To ResSub
StrgSql = StrgSql & "[" & Me("Text2") + IntCounter & "]" &
Me(Text2) + IntCounter & Chr$(34) & " And "
Next

'Strip Last " And "
If StrgSql <> "" Then
StrgSql = Left(StrgSql, (Len(StrgSql) - 5))
'Set Filter Property
DoCmd.OpenReport "RepBankTotalTran", A_PREVIEW 'Open report.
Reports![RepBankTotalTran].Filter = StrgSql
Reports![RepBankTotalTran].FilterOn = True
End If
Else
'Set Filter for ResSub zero value
StrSql = StrgSql & "[" & Me("Text1") + IntCounter & "]" &
Me("Text1") + IntCounter & Chr$(34) & " And "
'Strip Last " And "
If StrgSql <> "" Then
StrgSql = Left(StrSql, (Len(StrSql) - 5))
'Set Filter Property
DoCmd.OpenReport "RepBankTotalTran", A_PREVIEW 'Open report.
Reports![RepBankTotalTran].Filter = StrSql
Reports![RepBankTotalTran].FilterOn = True
End If
End If
End If
End Sub

The error I am Getting:
the Expression ‘[711]’ you enter id invalid.
What I am trying to do is to create a string with and between the dates value
Like 0710&0711&0712&0713&0714&0715 then filter table in report
“RepBankTotalTranâ€. This will get me only records no to total Please help or
u guys if u know any other way to do that

Sincerely
The Master
 
P

Pieter Wijnen

Use the In operator

ie resulting where clause

WHERE MyDate IN ('0710','0711')

HTH

Pieter



Master said:
Tran date Total Tran Tran ID Merch Name Bank Name Term ID
0710 22 247 EBS Center El Nilein Indstrial Development Bank G 12345678
0711 63 262 EBS Center El Nilein Indstrial Development Bank G 12345678
0714 5 538 Bank El Mozaree POS El Nilein Indstrial Development Bank
G 12345678
0715 2 599 Bank El Mozaree POS Farmers Commerical Bank 22111000
0717 7 617 EBS Center El Nilein Indstrial Development Bank G 12345678
0718 7 620 EBS Center El Nilein Indstrial Development Bank G 12345678
0721 3 645 Masraf El Mozare3 Pharmacy Farmers Commerical Bank 22111000

This How my table look like I inherit this table with date column
VarChar(50).
What I would like to do is to get total Tran Id Between 2 dates let say
0710
and 0715
I did a code in access to get me all records between 2 dates by creating a
form with 2 combo box pointing to the date column and a button with the
following command

Private Sub Command4_Click()
'Table: have Bank name VarChar (50), Bank transactions, Transactions
date VarChar (50), Total Transactions (for all banks)
'Required: filter by Bank name and/or transaction between 2 dates (Date
column type is VarChar (50)).
'Able to filter by Bank name.
'Using Access.
'Have 2 combo Box Start date (Text1), End date (Tesxt2).
Dim StrSql As String, StrgSql As String, IntCounter As Integer, ResSub
As
Integer
StrSql = Text1
StrgSql = Text2
If Me("Text1") <> "" Then
If Me("Text2") <> "" Then
ResSub = StrgSql - StrSql
End If
End If
'Set Filter for ResSub positive value
If ResSub > 0 Then
For IntCounter = 1 To ResSub
StrSql = StrSql & "[" & Me("Text1") + IntCounter & "]" &
Me("Text1") + IntCounter & Chr$(34) & " And"
Next

'Strip Last " And "
If StrSql <> "" Then
StrSql = Left(StrSql, (Len(StrSql) - 5))
'Set Filter Property
DoCmd.OpenReport "RepBankTotalTran", A_PREVIEW 'Open report.
Reports![RepBankTotalTran].Filter = StrSql
Reports![RepBankTotalTran].FilterOn = True
End If
Else
'Set Filter for ResSub negative value
If ResSub < 0 Then
ResSub = -1 * ResSub
For IntCounter = 1 To ResSub
StrgSql = StrgSql & "[" & Me("Text2") + IntCounter & "]" &
Me(Text2) + IntCounter & Chr$(34) & " And "
Next

'Strip Last " And "
If StrgSql <> "" Then
StrgSql = Left(StrgSql, (Len(StrgSql) - 5))
'Set Filter Property
DoCmd.OpenReport "RepBankTotalTran", A_PREVIEW 'Open report.
Reports![RepBankTotalTran].Filter = StrgSql
Reports![RepBankTotalTran].FilterOn = True
End If
Else
'Set Filter for ResSub zero value
StrSql = StrgSql & "[" & Me("Text1") + IntCounter & "]" &
Me("Text1") + IntCounter & Chr$(34) & " And "
'Strip Last " And "
If StrgSql <> "" Then
StrgSql = Left(StrSql, (Len(StrSql) - 5))
'Set Filter Property
DoCmd.OpenReport "RepBankTotalTran", A_PREVIEW 'Open report.
Reports![RepBankTotalTran].Filter = StrSql
Reports![RepBankTotalTran].FilterOn = True
End If
End If
End If
End Sub

The error I am Getting:
the Expression '[711]' you enter id invalid.
What I am trying to do is to create a string with and between the dates
value
Like 0710&0711&0712&0713&0714&0715 then filter table in report
"RepBankTotalTran". This will get me only records no to total Please help
or
u guys if u know any other way to do that

Sincerely
The Master
Click to expand...
 
Top