Hi all,
I'm a newbie to MS Access & trying to make a report with from a table as below : -
ItemID CreDate/DeliDate/CompleDate
-
A 01-Jan
-
B 02-Jan
-
C 02-Jan 04-Jan
-
D 03-Jan 05-Jan 07-Jan
-
E 04-Jan 06-Jan 08-Jan
-
My expected outcome for the report is like - 1. CreateDate : A,B
-
2. DeliveryDate : C
-
3. CompleteDate : D, E
I tried to refer to some other similar cases in this site but they are quite different. If you have any suggestion, pls help.
Thanks.
namktkt,
To slightly modify the code you provided: - Public Function BuildString(ColumnNum As Integer)
-
Dim db As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim fAddText As Boolean
-
Dim strReturn As String
-
-
strReturn = ""
-
fAddText = False
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset("Table1", dbOpenSnapshot)
-
If Not rst.RecordCount = 0 Then
-
rst.MoveFirst
-
Do Until rst.EOF
-
Select Case ColumnNum
-
Case 1
-
If (Not IsNull(rst!CreDate)) _
-
And IsNull(rst!DeliDate) Then
-
fAddText = True
-
End If
-
Case 2
-
If (Not IsNull(rst!DeliDate)) _
-
And IsNull(rst!CompleDate) Then
-
fAddText = True
-
End If
-
Case 3
-
If Not IsNull(rst!CompleDate) Then
-
fAddText = True
-
End If
-
End Select
-
If fAddText Then
-
strReturn = strReturn & _
-
IIf(strReturn = "", rst!ItemID, _
-
", " & rst!ItemID)
-
End If
-
fAddText = False
-
rst.MoveNext
-
Loop
-
Set rst = Nothing
-
Set db = Nothing
-
End If
-
End Function
Then, just call the Code from your Query:
Will give you A,B (based on your sample data) - BuildString(2)
-
-
and
-
-
BuildString(3)
Will produce results for your other two columns.
22 1480
namktkt,
You mention you are a newbie to MS Access, so I will tell you that the solution might be more involved than what you might be looking for.
To describe how I would approach this problem, I would: - Call a Function that uses as its parameter a numerical flag (1, 2 or 3), representing the three different return values you desire. This function would return a string value that you use in your report.
- The Function would create a recordset, based on your table, searching for records that meet the criteria you describe:
- Create Date, but no Delivery Date/Complete Date
- Delivery Date but no Complete Date
- Complete Date
- The Function would cycle through the records, adding the Item ID to the String (plus a ", " between records)
- The Value returned by that Function would go in your report.
I hope this description makes sense. For those of us who have been doing this a long time, what you are asking for is (should be) relatively simple. However, we don't even know your Table names or what your data looks like.
On this site, we prefer to see the work our posters have already tried and troubleshoot problem areas to correct/prevent errors.
We are standing by to assist further.
Hi twinnyfo,
When u say about writing a Function, I'm a bit worried as I'm not so confident in writing a Function in Access. But anyway, I tried & did it (as below).
As you can see in my file, we can get the outcome of the Function but it's quite stupid to me that I can't assign these output values to the Report !!! Can u help me this ?
And another question is that can we arrange output values in better order (it is currently "F1,F,E1,E,D,", can we change to D,E,E1,F,F1) ?
Thks, -
Dim db As DAO.Database, rst As DAO.Recordset
-
Dim strCreDate As String, strDeliDate As String, strCompleDate As String
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset("Table1", dbOpenSnapshot)
-
rst.MoveFirst
-
-
Do Until rst.EOF
-
If Not IsNull(rst.Fields("CreDate")) = True And IsNull(rst.Fields("DeliDate")) = True Then
-
strCreDate = rst.Fields("ItemID") & ", " & strCreDate
-
End If
-
If Not IsNull(rst.Fields("DeliDate")) = True And IsNull(rst.Fields("CompleDate")) = True Then
-
strDeliDate = rst.Fields("ItemID") & ", " & strDeliDate
-
End If
-
If Not IsNull(rst.Fields("CompleDate")) = True Then
-
strCompleDate = rst.Fields("ItemID") & "," & strCompleDate
-
End If
-
rst.MoveNext
-
Loop
-
-
Set rst = Nothing
-
Set db = Nothing
-
-
DoCmd.OpenReport "Table1", acViewPreview
-
zmbd 5,501
Expert Mod 4TB
is this the actual text you want from the data given? -
-
1. CreateDate : A,B
-
2. DeliveryDate : C
-
3. CompleteDate : D, E
-
-
so for line item one: A, B and not 01-Jan , 02-Jan
Do you need the item list numbers (1 thru 3...)
Yes, I need the data is A,B...(not the date).
The number 1,2,3 is not necessary, so don't pay attention to it.
Anyway, with my codes, I can see that I was able to list the data to A,B,C...but now I don't know how to assign them to the Report (sorry I can't upload my file here). I tried to put "strCreDate", "strDeliDate", "strCompleDate" to the ControlSource of 3 different textboxes in the report. But it doesn't show the values !!!
Namktkt,
I think you might want to take a slightly different approach on this. What you are trying to accomplish is very common in Access Reporting except for putting all the values on one line.
Typically, by creating a Report with Grouping, you will get an output very close to what you want except the values (A, B, C, D, E...) will be on separate lines. A big reason for this is that when more and more data is added to the table, the output lines would get longer and longer and at some point would need to wrap around and start a new line. When this happens, the output starts to get confusing.
I think you will also find that since Access was tailored this way, it will be much less frustrating to accomplish.
namktkt,
What I meant by using a function is that you call the function from within the Query. The results of the function then serve as one of your fields, which you can place anywhere on th Report.
However, please consider carefully the advice from jforbes (Post #6). Although we can certainly guide you to a solution that will "look and feel" exactly like you want it, it may be "easier and wiser" to use some of teh built-in functionality of the MS Access reporting capabilities.
It is your choice, and we can work through either solution.
namktkt,
To slightly modify the code you provided: - Public Function BuildString(ColumnNum As Integer)
-
Dim db As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim fAddText As Boolean
-
Dim strReturn As String
-
-
strReturn = ""
-
fAddText = False
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset("Table1", dbOpenSnapshot)
-
If Not rst.RecordCount = 0 Then
-
rst.MoveFirst
-
Do Until rst.EOF
-
Select Case ColumnNum
-
Case 1
-
If (Not IsNull(rst!CreDate)) _
-
And IsNull(rst!DeliDate) Then
-
fAddText = True
-
End If
-
Case 2
-
If (Not IsNull(rst!DeliDate)) _
-
And IsNull(rst!CompleDate) Then
-
fAddText = True
-
End If
-
Case 3
-
If Not IsNull(rst!CompleDate) Then
-
fAddText = True
-
End If
-
End Select
-
If fAddText Then
-
strReturn = strReturn & _
-
IIf(strReturn = "", rst!ItemID, _
-
", " & rst!ItemID)
-
End If
-
fAddText = False
-
rst.MoveNext
-
Loop
-
Set rst = Nothing
-
Set db = Nothing
-
End If
-
End Function
Then, just call the Code from your Query:
Will give you A,B (based on your sample data) - BuildString(2)
-
-
and
-
-
BuildString(3)
Will produce results for your other two columns.
Thanks jforbes, I understand that u're guiding me another approach to my issue. But it seems to tough for me to get your points (I'm quite new to Access !!!).
Thanks twinnyfo, your modified codes look much better than mine. Please let me check at my end & get back to u soon.
Hi twinnyfo,
I copy your codes in the Modules under the name of Module1. I tested it by using AddWatch with the strReturn & I can see the output of the Function.
However, when I set up a textbox on Report & set =BuildString(1) in the ControlSource, the Report only displays blank for this Textbox at the preview mode. Am I wrong somewhere else ?
Thks
BuildString(1) (and the other two), should be Fields in your Query. - SELECT BuildString(1) AS CreateDate ... (etc.)
Then you use the Field the Query Returns (CreateDate) in your Report. I try to avoid Calculating in my Reports unless I have to.
I've got it & try your suggestion but it fails !!!
Anyway, let me check this error around & get back to u. Thks.
Please provide the Query you use as the REcord Source for your Report.
I set up 1 Query & use it as Record Source for the Report.
It is "SELECT BuildString(1) AS CreateDate, BuildString(2) AS DeliveryDate, BuildString(3) AS CompleteDate
FROM Table1;"
Hi twinnyfo,
Table1 is requested by Access to execute the query. When I try to run the query alone, it also doesn't have any value inside (except for some blank cells) !!!
From Post #14, this is the ONLY recordsource for your Report? THere are not other fields or controls, just the three bits of information?
Remove the "From Table1" - SELECT BuildString(1) AS CreateDate, BuildString(2) AS DeliveryDate, BuildString(3) AS CompleteDate;
I tried this but one msgbox saying that "Query input must contain at least one table or query" :(
I'm using MS Access 2007 SP2.
Then ignore the Record Source. have an unbound Report (no record source). Then have three text box controls (also unbound). For their Control Source, make them: - = BuildString(1)
-
=BuildString(2)
-
=BuildString(3)
This should work. Still don't know why this did not work from Post #10.
Yes, i created a new report & follow your way. It's still nothing there.
Let me try on another PC by tomorrow. Thanks
I found the error, twinnyfo.
U missed one line in your codes: -
Loop
- BuildString = strReturn
-
Set rst = Nothing
-
So, now I can run the function directly in Report (no need to set up a Query then).
It's done.
Thanks guy
Ummmmmmm, yeah! Call me dumb!
:-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jean-Claude Bertrand |
last post by:
Hello to all .Net programmers!
I am a consultant and I create very small application for small enterprise.
Most of the time in my application, the number of users is under 5! I still
program...
|
by: tbehmand |
last post by:
Hi I am getting the following message when I click on 'Create report in Design view', does anyone know how to fix this.
"There was a problem retrieving printer information for this object. The...
|
by: alem |
last post by:
Hi all
help me how to create Report in SQL Server or SQL Query Analyzer.
thank you in advance
|
by: alem |
last post by:
Hallo
pls help me how to create Report in Visual Basic.
|
by: SantyBhi |
last post by:
Plz ,
any one can explain how to create report in c#asp.net without using Crystal Report.
|
by: bdmir |
last post by:
Hello,
I have a text file(tab delim) with data and I want to create report. Here is the content of text file:
HD John Smith j.smith@testmail.com
CT service 1 5$
CT service2 1 6$
CT service3 4...
|
by: michael george |
last post by:
Hi,
I am a recreational user of access and I enjoy making small databases. My question is when I add records to a form i want to create a report on the form currently displayed on screen, at the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |