Solved

Open feltered report by a date coming from a combo box

Posted on 2012-04-10
9
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

735 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