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...
dugong
Last Activity: Jan 2 '09, 04:27 AM
Joined: Nov 17 '08
Location: er?
-
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...Leave a comment:
-
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...Leave a comment:
-
My query code:
The error message is:Code:SELECT t_2.* , concat (t_2.gh) AS rdatelist FROM t_2 ;
"Data type mismatch in criteria expression"
Does this mean gh is not formated correctly or what?
regardsLeave a comment:
-
Actually i've tried to change the original module to this one:
...Code:Option Compare Database Public Function Concat(lngID As Variant) As Variant If IsNull(lngID) Then Exit Function Dim rs As New ADODB.Recordset With rs .ActiveConnection = CurrentProject.Connection .CursorType = adOpenForwardOnly .LockType = adLockReadOnlyLeave a comment:
-
Ok.Here's one issue:
...Code:Public Function Concat(lngID As Variant) As Variant If IsNull(lngID) Then Exit Function Dim rs As New ADODB.Recordset With rs .ActiveConnection = CurrentProject.Connection .CursorType = adOpenForwardOnly .LockType = adLockReadOnly .Open "SELECT t1.* FROM tblValues AS t1 INNER JOIN"Leave a comment:
-
Many thanks Fishval!
I'll definitely look into it and show how far can i go with this one....Leave a comment:
-
Ok,i've found out something.
I've changed the transform statement from my current code :
...Code:PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value; TRANSFORM Count(t_2.gh) AS cnt1 SELECT t_2.gh FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate GROUP BY t_2.gh PIVOT Format([dteDate],"dd-mm-yyyy");
Leave a comment:
-
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...Leave a comment:
-
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...Leave a comment:
-
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...Leave a comment:
-
Sorry about the incovenient.Her e's the MetaData:
Original qtsj.dbf
t_2 table (coming from make-table query)Code:gh - text rq - text hs1 - number ms1 - number wn - text (hardware serial number)
...Code:gh - text year - text month - text day - text zdate - time/date (formated , 2-12-2008) rtime - text (eg: 8:40)
Leave a comment:
-
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...Leave a comment:
-
Thanks for your reply.I've followed your suggestion and of course it works!
Code:PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value; TRANSFORM Count(t_2.gh) AS cnt1 SELECT t_2.gh FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate GROUP BY t_2.gh PIVOT Format([dteDate],"dd-mmm-yyyy");
But now my problem is to...Leave a comment:
-
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...Leave a comment:
-
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...Leave a comment:
-
Ok, another issue arise
I have this code:
So i want to search a date (fdate) using this query:Code: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 FROM qtsj;
...Code:SELECT Query1.fdate FROM Query1
Leave a comment:
-
Update:
I've found out the solution for the error.
I must put both
Forms!monthly_r apport!combo10 and Forms!monthly_r apport!combo8 to my cross-tab query parameter.
Thanks again.Leave a comment:
-
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_r apport!combo10
...Code:TRANSFORM Count(t.keyID) AS cnt1 SELECT t.gh FROM t RIGHT JOIN qry1Month ON t.dte=qry1Month.dteDate
Leave a comment:
-
Thanks.It works just fine!
But the format is (year , month , date )
regards...Leave a comment:
No activity results to display
Leave a comment: