By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Access TRANSFORM (Crosstab) Queries and Missing Rows

P: 33
Here is my query.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(t_date.ftime) AS Sumftime
  2. SELECT t_date.gh
  3. FROM t_date
  4. GROUP BY t_date.gh
  5. PIVOT t_date.fdate;
What i'm doing is count the number of clocking per points number. My problem is if there is no clocking for one date, it will not show this date in the query.


So i will only have several dates in a month instead of full days and months.Is there a work around for this?


Thanks


p/s:i've attached my query result with this post


Nov 17 '08 #1
Share this Question
Share on Google+
42 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

There are at least two ways to solve the problem.
  • The first is to use TRANSFORM .... PIVOT ... IN (<valueslist>) syntax. To automate the process of <valueslist> adding you will need to design VBA function returning list of sequential dates and put it to query via correspondent Querydef object.
  • The second is to :
    • Generate dataset of sequential dates.
    • Outer join it with your table on date field.
    • Perform crosstab query.
    • Remove empty row generated from empty dates.
    An example of this is in attachment below.

Regards,
Fish
Attached Files
File Type: zip CrosstabWithOuterJoin.zip (13.4 KB, 471 views)
Nov 17 '08 #2

P: 33
Hello, dugong.

There are at least two ways to solve the problem.
  • The first is to use TRANSFORM .... PIVOT ... IN (<valueslist>) syntax. To automate the process of <valueslist> adding you will need to design VBA function returning list of sequential dates and put it to query via correspondent Querydef object.
  • The second is to :
    • Generate dataset of sequential dates.
    • Outer join it with your table on date field.
    • Perform crosstab query.
    • Remove empty row generated from empty dates.
    An example of this is in attachment below.

Regards,
Fish
Thanks for your reply Fish.

So whether i pick solution no 1 or 2 , i still need to manually create an external table or key-in all possible months,days and years in mind.

How if my data reach a date beyond 2010 or 2020? I need to calculate the leap year to obtain 29 days february...

Is there any way i could pull the date from the pc date and time and create a table from it?


regards
Nov 18 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

Thanks for your reply Fish.

So whether i pick solution no 1 or 2 , i still need to manually create an external table or key-in all possible months,days and years in mind.
How if my data reach a date beyond 2010 or 2020? I need to calculate the leap year to obtain 29 days february...
Going with solution #2, I would create records in [tblYears] up to, let us say, 2100 releaving myself of responsibility for future crash.
No matter whether you go with solution #1 or #2 a leap year date is being processed normally.

Is there any way i could pull the date from the pc date and time and create a table from it?
Mmmm. Sure, there is a way. Just, out of curiosity, what are advatages of this way?

Regards,
Fish
Nov 18 '08 #4

P: 33
Hello, dugong.



Going with solution #2, I would create records in [tblYears] up to, let us say, 2100 releaving myself of responsibility for future crash.
No matter whether you go with solution #1 or #2 a leap year date is being processed normally.



Mmmm. Sure, there is a way. Just, out of curiosity, what are advatages of this way?

Regards,
Fish

Hello Fish,

I wanted to pull the date from pc clock because i wanted to avoid creating date table scretching to years...

What i mean is,each time my little report generator (access query) started it will check the date first,compare it and then it will add the missing date (days,months and years) into the date table automaticly.

However problem will arise if the PC clock is inaccurate...


regards
Nov 18 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Hello Fish,

I wanted to pull the date from pc clock because i wanted to avoid creating date table scretching to years...
Hmm. I'm not sure what exactly you want to avoid by avoiding table with 100-150 records. BTW, [tblYears] could be replaced with a query returning distinct years from your table thus ensuring that years list is always relevant to database content. Or the list returned by the query could update [tblYears], thus caching it and avoiding performance penalty.

What i mean is,each time my little report generator (access query) started it will check the date first,compare it and then it will add the missing date (days,months and years) into the date table automaticly.
IMHO solution #1 - creation of delimited list of sequential dates, is the same but without that temporary table of dates you want (still without any obvious for me reason). It will require the same code, but different way to use obtained values - concatenating into delimited string vs. storing in temporary table.

However problem will arise if the PC clock is inaccurate...
:) Sure.

Kind regards,
Fish
Nov 18 '08 #6

P: 33
Thank you for your replies,

I still need some help to calculate total days for each month.What i know is

Jan - 31
Feb - 28 or 29
Mar - 31
Apr - 30
May - 31
June - 30
July - 31
Aug - 31
Sept - 30
Oct - 31
Nov - 30
Dec - 31

Do i need to create a separate table for days or write an algorithm to calculate the different days in VBA?

My report will be based on month of year.Something like this :

Points | Jan 2008 | 01 | 02 | 03 | 04 | 05
00xxx1 10 11
00xxx2 11 10
00xxx3 10 10
00xxx4 12 10


and the same format for each month....
Nov 19 '08 #7

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

You don't need to know how many days in each month as well as you don't need to implement date logic manually. Below is a simple code iterating all days of current month. Pay attention on using of Date(), DateSerial(), Month() and Year() functions. VBA has many functions to opearate with date type variables.

Expand|Select|Wrap|Line Numbers
  1. Public Function DaysOfCurrentMonth()
  2.  
  3.     Dim dte As Date
  4.  
  5.     'get first day of current month
  6.     dte = DateSerial(Year(Date), Month(Date), 1)
  7.  
  8.     'iterate while the date is still within current month
  9.     While Month(dte) = Month(Date)
  10.         Debug.Print dte
  11.         'increment date
  12.         dte = dte + 1
  13.     Wend
  14.  
  15. End Function
  16.  
Regards,
Fish
Nov 19 '08 #8

P: 33
Thanks for your replies.

I got another question to ask:

How to insert a form into this query?

Expand|Select|Wrap|Line Numbers
  1. SELECT qryFlatCalendar.dteDate
  2. FROM qryFlatCalendar
  3. WHERE (((qryFlatCalendar.dteDate)<=#1/31/2008# And (qryFlatCalendar.dteDate)>=#1/1/2008#));
  4.  

I tried this one...but not possible...

Expand|Select|Wrap|Line Numbers
  1. SELECT qryFlatCalendar.dteDate
  2. FROM qryFlatCalendar
  3. WHERE (qryFlatCalendar.dteDate) <= #31-01-(Forms!monthly_rapport!combo10)#;
  4.  


Thanks again.
Nov 26 '08 #9

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

Use DateSerial() function.

Expand|Select|Wrap|Line Numbers
  1. DateSerial(31, 1, Forms!monthly_rapport!combo10)
  2.  
Nov 26 '08 #10

P: 33
@FishVal

Thanks.It works just fine!

But the format is (year , month , date )

regards
Nov 26 '08 #11

P: 33
The form problem is still haunting me...

an error message appear when i tried to run t_Crosstab query

The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)

name in this case is Forms!monthly_rapport!combo10

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(t.keyID) AS cnt1
  2. SELECT t.gh
  3. FROM t RIGHT JOIN qry1Month ON t.dte=qry1Month.dteDate
  4. GROUP BY t.gh
  5. PIVOT Format([dteDate],"dd-mmm-yyyy");
  6.  
Nov 27 '08 #12

P: 33
Update:

I've found out the solution for the error.

I must put both

Forms!monthly_rapport!combo10 and Forms!monthly_rapport!combo8 to my cross-tab query parameter.



Thanks again.
Nov 27 '08 #13

P: 33
Ok, another issue arise

I have this code:

Expand|Select|Wrap|Line Numbers
  1. SELECT qtsj.rq, Left(rq,4) AS [year], Mid(rq,5,2) AS [month], Right(rq,2) AS [day], [day] & "-" & [month] & "-" & [year] AS [date], Format([date],"dd-mm-yyyy") AS fdate
  2. FROM qtsj;
  3.  
So i want to search a date (fdate) using this query:

Expand|Select|Wrap|Line Numbers
  1. SELECT Query1.fdate
  2. FROM Query1
  3. WHERE Query1.fdate Between [Forms]![record_search]![Text0] And [Forms]![record_search]![Text1];
  4.  
with the date format inserted in the form dd-mm-yy

e.g 01-10-2008

but my query seems don't work..
Nov 28 '08 #14

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

@dugong
  • What is the type of [rq] field? You treat it as String, but it could be Date as well. If you are not sure about type, then check it with the following query:
    Expand|Select|Wrap|Line Numbers
    1. SELECT TypeName(qtsj.rq) FROM qtsj;
    2.  
  • You use field alias names ([year], [month], [day], [date]) for further calculations in the same query where they are introduced. SQL syntax doesn't allow this.
  • [date] field is certainly of String type, and could not be formatted in a way you try to do to obtain [fdate].
  • You don't use Left() and Right() functions properly in context of expcted format of [rq] field.
  • String type data returned by the query could not be compared in the second query in a proper way with form control values.
Nov 28 '08 #15

P: 33
@FishVal
Actually qtsj.dbf is one of linked table from a foxpro software (if i've not mistaken) that we have in the office.I wanted to create a simple report generator based from the original one as it has bugs.

-Using the command,qtsj.rq is a string but indeed it's a date i just didnt realise it's treated as a string.So do i need to copy the original table and reformat the field?

-I use those commands merely as a 'makeup' to be able to match the date format in a calendar form i've found on the net.

the format from qtsj.rq is yyyymmdd while in the calendar form is dd-mm-yyyy

Is there any easy way to solve this problem?
Attached Images
File Type: jpg problem.jpg (15.6 KB, 412 views)
Dec 1 '08 #16

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

If you want to use values from [qtsj] as dates - e.g. compare with dates from the form, you have no option but to convert them to date values, otherwise comparisson operations (particularly BETWEEN ... AND ... you use in your query) will not work as expected.

To achieve this I suggest you to parse string value to year, month, day parts (as you've already done), convert them to number values using Val() function and use thus obtained values as DateSerial() function arguments to obtain date value.

Regards,
Fish
Dec 1 '08 #17

P: 33
@FishVal
Thanks for your reply.

But i'm kinda stuck right now , here's my linked dbf files that come from the original software. (reborn v0.3)

QTSJ.dbf is hardware generated data (points no,clocking date and time) while RSDA.dbf is user generated data (points no,points name,group and order no)

Based on your example in crosstab query, i must put an autonumber field in the table. But this feature isn't available in make-table query.So how does i count the clocking sums for each day?

My real plan is to create a report not only showing clocking sums per points number for each day.But also the clocking time for each day!

Screenshot below (monthly_report)
Attached Files
File Type: zip Reborn V0.3.zip (515 Bytes, 126 views)
File Type: zip monthly_report.zip (89.6 KB, 133 views)
Dec 2 '08 #18

FishVal
Expert 2.5K+
P: 2,653
@dugong
Hello, dugong.

Autonumber field was used as example only.
You may use any as long as it is unique within grouped range.
I guess [points no] will be suitable.
Or you could use Count(*) to count all records within grouped range.
Dec 2 '08 #19

P: 33
@FishVal
Thanks for your reply.I've followed your suggestion and of course it works!

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value;
  2. TRANSFORM Count(t_2.gh) AS cnt1
  3. SELECT t_2.gh
  4. FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate
  5. GROUP BY t_2.gh
  6. PIVOT Format([dteDate],"dd-mmm-yyyy");
  7.  

But now my problem is to insert 'qtsj.hs1' and 'qtsj.ms1' (or time) into the crosstab query according to points and sorted by date.

How is this possible since i must use transform function in the query? Obviously i don't want to make any mathematical operation for time..


Thanks
Attached Images
File Type: jpg example.jpg (12.1 KB, 359 views)
Dec 3 '08 #20

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

... insert 'qtsj.hs1' and 'qtsj.ms1' (or time) into the crosstab query according to points and sorted by date ...
What does it mean?
Dec 3 '08 #21

P: 33
@FishVal
Erm see screenshot above or download the screenshot (monthly_report.zip) if it's too small.

It will be something like this:

------------------------------------------------------------------------------

Points No | Sum | 01-Jan-08 ...............31-Jan-08

0xxxx1 ___ 2 _____14.30 ___________and so on
_________________14.45


0xxxx2 ___ 3 ____15.00____________and so on
________________ 15.05
_________________15.10

------------------------------------------------------------------------------------

It's like a monthly summary @ report with each points details pivoted by date and data inside is the corresponding time clocked for that date.


regards
Dec 3 '08 #22

FishVal
Expert 2.5K+
P: 2,653
Screenshots are great but don't make much sense for developer.
Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Dec 3 '08 #23

P: 33
@FishVal
Sorry about the incovenient.Here's the MetaData:

Original qtsj.dbf

Expand|Select|Wrap|Line Numbers
  1. gh - text
  2. rq - text
  3. hs1 - number
  4. ms1 - number 
  5. wn - text     (hardware serial number)
  6.  
t_2 table (coming from make-table query)

Expand|Select|Wrap|Line Numbers
  1. gh - text
  2. year - text
  3. month - text
  4. day - text
  5. zdate - time/date  (formated , 2-12-2008)
  6. rtime - text  (eg: 8:40)
  7.  


regards
Dec 3 '08 #24

FishVal
Expert 2.5K+
P: 2,653
Ok. It makes much more sense now.

So, I guess you need to join [t_2] and [qtsj] on [gh] field and use the resulting dataset in the query instead of [t_2].
However, it isn't clear to me what type of relationship is supposed to be between [t_2] and [qtsj].
Is that one-to-one or one-to-many. Also, I'm not sure whether it should be an outer or inner join.

Regards,
Fish
Dec 3 '08 #25

P: 33
@FishVal
t_2 is created to copy the data in qtsj and to format the date since qtsj is a linked table.so i use t_2 as qtsj replacement.

the one that must be related later is rsda.dbf with qtsj.dbf...or with my current data it would be rsda.dbf with t_2

(since i've replaced and reformated qtsj with t_2)

If it's possible please take a glance on my uploaded data (reborn v0.3).

am still stuck with the time problem,any ideas?
Dec 3 '08 #26

FishVal
Expert 2.5K+
P: 2,653
Ok.

I imported rsda.dbf to Access database, for qtsj.dbf it says "External table is not in expected format".

Hmm..

What could I say. Neither content, no field names make much sense.
So, how these two datasets are supposed to be treated together?

Kind regards,
Fish.
Dec 3 '08 #27

P: 33
@FishVal
To be able to link the qtsj table you must use the odbc driver.

The data above is merely an example,here with my post i attach a real clocking data from one of our clients.The rsda.dbf table come from the user who manually key-in the required information.

Here's what rsda.dbf real data look like:



The required field are:

1.Number
(Obviously we must know the points serial number first before we can key-in the information)

2.Name
(Put a desired name which is normaly the location name where's the points is fixed)

3.Group Number
(If there's too many points,we can group the points into 10 per group.This group is actually used in search function)

4.Order
(The order of the points in the report)

*The description given are based on the original software.My goal is to create a simple monthly report generator based on the original software using linked qtsj.dbf and rsda.dbf tables in the original software folder.

Here's an example of the report that i wanted to create:



As you can see,there's Name field which actually come from rsda.dbf table and the order is also sorted from there.


Until this post,i haven't touched the rsda.dbf table part yet..but by following your guidance i've been able to create a month pivot table with sum for each day which will be used to create the report.

Am only lacking clocking time for each date....

(and of course the points name also which will be added later,until now i've only used the points serial number)

I think rsda.dbf and qtsj.dbf must be JOINED (Where rsda.dbf's points serial number = qtsj.dbf point's serial number ) to be able to create the complete report.


regards
Dec 4 '08 #28

P: 33
Ok,for the odbc driver i've used this one:

DSN=Visual FoxPro Tables

Some tutorial on how the original software works:

1.User key-in the points serial number,and then put a related name into the points number,group number and the order. (this will create the rsda.dbf table)

2.Guards go to each points which fixed in e.g real estate area to do clocking.Say that there's 10 points,so there will be 10 different points serial number and names.The time differences to go to each points location will create 10 different clocking time.

3.What clocking do in the sense of hardware:it will record the points serial number with time and date.

4.So that when someone download the data to the original software.It will create a qtsj.dbf file.

5.When the user goes to view report,the software will match the points serial number with an existing one in rsda.dbf.If there's no match,the software can only show the points serial number with date and time. (which is logic)

6.If there's a match,the report will show user the points number (with name given in rsda.dbf) clocking time pivoted by days in a month.


Hope this description helps.

here's the real data that i've screen-shooted above:

RapidShare: Easy Filehosting
Dec 4 '08 #29

P: 33
Ok,i've found out something.

I've changed the transform statement from my current code :

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value;
  2. TRANSFORM Count(t_2.gh) AS cnt1
  3. SELECT t_2.gh
  4. FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate
  5. GROUP BY t_2.gh
  6. PIVOT Format([dteDate],"dd-mm-yyyy");
  7.  
To this one:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value;
  2. TRANSFORM FIRST(t_2.rdate) AS cnt1
  3. SELECT t_2.gh
  4. FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate
  5. GROUP BY t_2.gh
  6. PIVOT Format([dteDate],"dd-mm-yyyy");
  7.  

Result:



But of course i can only 'pull' one time from this query for each date..

Any idea how to list all the time for the whole day?


Regards
Dec 5 '08 #30

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

Sorry for delay.
What you are looking for could be done with custom aggregate function.
Here I will attach database sample implementing such.

Regards,
Fish
Attached Files
File Type: zip CustomAggregate.zip (11.4 KB, 109 views)
Dec 8 '08 #31

P: 33
@FishVal
Many thanks Fishval!

I'll definitely look into it and show how far can i go with this one.
Dec 10 '08 #32

P: 33
Ok.Here's one issue:

Expand|Select|Wrap|Line Numbers
  1. Public Function Concat(lngID As Variant) As Variant
  2.  
  3.     If IsNull(lngID) Then Exit Function
  4.  
  5.     Dim rs As New ADODB.Recordset
  6.  
  7.     With rs
  8.  
  9.         .ActiveConnection = CurrentProject.Connection
  10.         .CursorType = adOpenForwardOnly
  11.         .LockType = adLockReadOnly
  12.         .Open "SELECT t1.* FROM tblValues AS t1 INNER JOIN" & _
  13.             " tblValues AS t2 ON t1.lngRowNum=t2.lngRowNum AND t1.txtColName=t2.txtColName" & _
  14.             " WHERE t2.keyValueID=" & lngID & ";"
  15.  
  16.         While Not .EOF
  17.             Concat = Concat & !txtValue & ";"
  18.             .MoveNext
  19.         Wend
  20.  
  21.         .Close
  22.  
  23.     End With
  24.  
  25.     Set rs = Nothing
  26.  
  27. End Function
  28.  
The KeyValueID is keep haunting me,my table is not using autonumber.

I only got gh (lngRowNum) , zdate (txtColName) and rtime (txtValue) but there's no KeyValueID.

How can i modify this module to suits my table?


Regards
Dec 10 '08 #33

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

[keyValueID] in the example is just a primary key.
It could be of any type (not only Autonumber), as long as it is unique in the table.
Do you have primary key or unique field in your table?

Regards,
Fish
Dec 10 '08 #34

P: 33
@FishVal
Actually i've tried to change the original module to this one:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function Concat(lngID As Variant) As Variant
  4.  
  5.     If IsNull(lngID) Then Exit Function
  6.  
  7.     Dim rs As New ADODB.Recordset
  8.  
  9.     With rs
  10.  
  11.         .ActiveConnection = CurrentProject.Connection
  12.         .CursorType = adOpenForwardOnly
  13.         .LockType = adLockReadOnly
  14.         .Open "SELECT t1.* FROM t_2 AS t1 INNER JOIN" & _
  15.             " t_2 AS t2 ON t1.gh=t2.gh AND t1.zdate=t2.zdate" & _
  16.             " WHERE t2.gh=" & lngID & ";"
  17.  
  18.         While Not .EOF
  19.             Concat = Concat & !rtime & ";"
  20.             .MoveNext
  21.         Wend
  22.  
  23.         .Close
  24.  
  25.     End With
  26.  
  27.     Set rs = Nothing
  28.  
  29. End Function
  30.  
My unique field is gh (points serial number) but it just don't work...was shown some error (exponentiel if i've not mistaken)

Here's my table:

Expand|Select|Wrap|Line Numbers
  1. gh - text
  2. year - text
  3. month - text
  4. day - text
  5. zdate - time/date  (formated , 2-12-2008)
  6. rtime - text  (eg: 8:40)
  7.  

Thanks again for your help!


regards
Dec 10 '08 #35

P: 33
My query code:

Expand|Select|Wrap|Line Numbers
  1. SELECT t_2.* , concat (t_2.gh) AS rdatelist
  2. FROM t_2 ;
  3.  
The error message is:
"Data type mismatch in criteria expression"

Does this mean gh is not formated correctly or what?


regards
Dec 10 '08 #36

FishVal
Expert 2.5K+
P: 2,653

P: 33
@FishVal
Hi FishVal,

Could you tell me whether i'm in the right direction or not? Are (') and (") the only problem in my code?

I've already checked the link given several times and i've tried to compare my code one-by-one, it seems all fine.I'm just confused..


regards
Dec 11 '08 #38

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

Check code of Concat() function.

Expand|Select|Wrap|Line Numbers
  1. .Open "SELECT t1.* FROM t_2 AS t1 INNER JOIN" & _
  2.             " t_2 AS t2 ON t1.gh=t2.gh AND t1.zdate=t2.zdate" & _
  3.             " WHERE t2.gh=" & lngID & ";"
  4.  
Value of lngID variable has to be enclosed in quotes as it is compared with Text type field.
" WHERE t2.gh='" & lngID & "';"

Regards,
Fish.
Dec 11 '08 #39

P: 33
@FishVal

Ok.I've changed the code and re-run the query but i get this error:

Syntax error (missing operator) in query expression 't2.gh='

I've tried again with " "& IngID & " ";" but i was shown

Syntax error


did we missed something else on the code?


regards
Dec 12 '08 #40

FishVal
Expert 2.5K+
P: 2,653
There is a rule of thumb when dealing with SQL expressions generated and used in code.
Get runtime value and try to execute it in query editor.

Regards,
Fish.
Dec 12 '08 #41

P: 33
@FishVal
I'm pretty new with sql.Could someone tell me how to do this? What runtime value required and what query editor is recommended?


Thanks
Dec 17 '08 #42

FishVal
Expert 2.5K+
P: 2,653
Hello, dugong.

What goes after ".Open" in code is being evaluated at run time to a string containing SQL expression.
VBA is of no help as for debugging SQL expressions being used in code.
So, if you need to clarify what is wrong with your SQL expression you need to obtain somehow the real value passed to Open method and debug it in query editor (aka query design view).
  • Make thefollowing changes to the code of Concat() function
    Expand|Select|Wrap|Line Numbers
    1. Option Compare Database
    2.  
    3. Public Function Concat(lngID As Variant) As Variant
    4.  
    5.     If IsNull(lngID) Then Exit Function
    6.  
    7.     Dim rs As New ADODB.Recordset
    8.     Dim strSQL As String
    9.  
    10.     strSQL="SELECT t1.* FROM t_2 AS t1 INNER JOIN" & _
    11.             " t_2 AS t2 ON t1.gh=t2.gh AND t1.zdate=t2.zdate" & _
    12.             " WHERE t2.gh='" & lngID & "';"
    13.     Debug.Print strSQL
    14.  
    15.     With rs
    16.  
    17.         .ActiveConnection = CurrentProject.Connection
    18.         .CursorType = adOpenForwardOnly
    19.         .LockType = adLockReadOnly
    20.         .Open strSQL
    21.  
    22.         While Not .EOF
    23.             Concat = Concat & !rtime & ";"
    24.             .MoveNext
    25.         Wend
    26.  
    27.         .Close
    28.  
    29.     End With
    30.  
    31.     Set rs = Nothing
    32.  
    33. End Function
    34.  
  • When the code fails, go to VBA window -> Immediate pane. If Immediate pane is not visible press Ctrl-G
  • Where you will find runtime value of SQL expression used as parameter of Open method.
  • Copypaste it to query design SQL view.

Regards,
Fish
Dec 17 '08 #43

Post your reply

Sign in to post your reply or Sign up for a free account.