Solved

Open feltered report by a date coming from a combo box

Posted on 2012-04-10
9
344 Views
Last Modified: 2012-04-12
Hi:
I had try plenty of such statements below
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Me.cboFeedingDate.Column(2) & "#", acWindowNormal
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Format(Me.cboFeedingDate.Column(2), "dd/mm/yy") & "#", acWindowNormal
 DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= " & Format(Me.cboFeedingDate.Column(2), "dd/mm/yy"), acWindowNormal
So and so ….
But I couldn’t get the right solution, always I get 0 records.
I have to choose a specific date from a combobox, of row source is " SELECT [tblTransactions].[ID], [tblTransactions].[TransType], [tblTransactions].[TransDate] FROM tblTransactions WHERE [tblTransactions].[TransType]=1 ORDER BY [TransType]; "
And my query behind the report is " SELECT tblTransactions.ID, tblItems.ItemName, tblTransactions.TransDate, tblTransactionsItems.Quantity_Retail, tblTransactions.TransType, tblSuppliers.Supplier FROM ((tblTransactions LEFT JOIN tblTransactionsItems ON tblTransactions.ID = tblTransactionsItems.TransID) LEFT JOIN tblItems ON tblTransactionsItems.ItemID = tblItems.ID) LEFT JOIN tblSuppliers ON tblTransactions.SupplierID = tblSuppliers.SupplierID WHERE (((tblTransactions.TransType)=1)) ORDER BY tblSuppliers.Supplier;"
I always display the generated (Me.cboFeedingDate.Column(2)), in msgbox and it shows appropriate date, but I don’t understand what is the problem .
Note that my database is Gregorian date and not hijri date.
Please help
Query-Behined-my-Report.JPG
My-report-results.JPG
0
Comment
Question by:Mohammad Alsolaiman
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829973
Try it like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" &  "#" & Me.cboFeedingDate.Column(2) & "#"

Post a sample of the DB that has this issue

Are you quite sure that you have records matching that date?

Are you quite sure that none of your dates actually contain a "Time" component that is simply hidden by formatting?

What happens  if you just put a textbox on the form and enter the date there, then do something like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" & "#" & Me.txtFeedingDate & "#"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37830008
...it also might have something to do with your regional settings too...
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37830865
here is a sample of my db
please try to open form "frmReportsMenue"
then choose report No# 3 from the lest "because of the list is in arabic"
good luck
-Inventory-be2.accdb
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37830907
First try the suggestion i posted, the report back.

<Try it like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" &  "#" & Me.cboFeedingDate.Column(2) & "#">

<What happens  if you just put a textbox on the form and enter the date there, then do something like this:
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]=" & "#" & Me.txtFeedingDate & "#">
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37830950
i had try the first one
but the text i'll try it at night (In sha'a allah)
thanks a lot : boag2000
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37835930
I had try all of them
"DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= " & Me.txtSupplier, acWindowNormal" dose not work  
"DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Me.txtSupplier & "#", acWindowNormal" this is work but i had to enter the date starting from year like this "2012/04/07" , but if i start backward  "07/04/2012" it will not work.
 as long i need it to be combo box , i'll try to backward  the selected item and see what will happen.
0
 

Author Comment

by:Mohammad Alsolaiman
ID: 37836919
Yes, OK
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Right(Me.cboFeedingDate.Column(2), 4) & "/" & Mid(Me.cboFeedingDate.Column(2), 4, 2) & "/" & Left(Me.cboFeedingDate.Column(2), 2) & "#", acWindowNormal
0
 

Author Closing Comment

by:Mohammad Alsolaiman
ID: 37836975
thanks a lot boag2000
here is the code when revers the date
DoCmd.OpenReport stDocName, acViewPreview, , "[TransDate]= #" & Right(Me.cboFeedingDate.Column(2), 4) & "/" & Mid(Me.cboFeedingDate.Column(2), 4, 2) & "/" & Left(Me.cboFeedingDate.Column(2), 2) & "#", acWindowNormal
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37838170
This is why a Sample database is Always helpful...
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

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