sSQL, Bhind parameter is not accept

gchwee
04-19-2004, 09:19 AM
sSQL = "{ChuHuo.Name} ='" & myname & "' AND {ChuHuo.Date} BETWEEN #" & datein & "# AND #" & dateout & "#"""
Report1.RecordSelectionFormula = sSQL 'ERROR

Sir, Soli for my post the same article for twice times. :)
Error is:
the behind sSQL is not recognise when passing it.
ERROR MSG: the remaining text do not appear to be part of formula.

P/S: if i post my same problem in bhind forum(same topic), will it notify you? or will u see it automatically?

Well, Thanks. : )

webbone
04-19-2004, 06:21 PM
You have an extra " at the end of your sSQL - it should be:


sSQL = "{ChuHuo.Name} ='" & myname & "' AND {ChuHuo.Date} BETWEEN #" & datein & "# AND #" & dateout & "#"

MKoslof
04-19-2004, 07:49 PM
And, I mentioned this before...open your Crystal Report. What is the NAME of the database fields given? Is it ChuHuo OR ADO? Since you are using the active data driver, your tables may now be aliased automatically. Go the report in design view and see how the table is referenced. You may have to change your declaration to ADO.Name and ADO.Date.

gchwee
04-19-2004, 09:46 PM
You have an extra " at the end of your sSQL - it should be:


sSQL = "{ChuHuo.Name} ='" & myname & "' AND {ChuHuo.Date} BETWEEN #" & datein & "# AND #" & dateout & "#"


Guy, Thanks for correct me. :) I appreciate.
Actually I did try the code u paste on because that is as same as the forum leader(MKoslof) give me before in last discussion. :) It is careless mistake when paste on forum. Sorry guy. :)
But it cannot work.
From the error msg: The remaining text dont appear to be part of programming.
Shall we add a "()" or "{}" to block the date range?
or shall we move the date range statement to infront and move the name paremeter bhind?
Any other better ideas?
I think is the sSQL not correct, izit? :)
Anywhere, u guy all so sweet. :)
Here really is a nice place to learning.
Thanks~

gchwee
04-19-2004, 09:57 PM
And, I mentioned this before...open your Crystal Report. What is the NAME of the database fields given? Is it ChuHuo OR ADO? Since you are using the active data driver, your tables may now be aliased automatically. Go the report in design view and see how the table is referenced. You may have to change your declaration to ADO.Name and ADO.Date.

Guy, is ChuHuo. Last time the statement i mistype it to "ChuoHuo", so the ERROR MSG = field name not recognise.
Sorry for my careless mistake. :) Sorry. MKoslof.

But I did correct it back and the ERROR MSG change :) (mean we are improving)

Now the ERROR MSG = The remainning text does not appear to be part of formula. I did try to change the "ChuHuo" to "ADO"(The report design view show that the field is refer by "ChuHuo", not ADO).
I also try the CVDATE(text1.text) and so on and so on from the .pdf file from CrystalReport official website.
But we fail to archieve it.

Shall we put somethings to block the "date range"? Like "()" or "{}"
or... shall we move the date range to in front?
Guys, forum leader-->MKoslof, appreciate u all helping.
Hope to hear from u all soon. :)

This is my last part of project, hope can settle it soon. :)
Add oil! ~~~ Yeah

MKoslof
04-20-2004, 07:58 AM
OK, this is why I like to use the CRAXDRT, Set DataSource method. With this method you can pass a STANDARD SQL query string as a recordset. With this type of method, a BETWEEN statement will work. Actually, since you are using the native .RecordSelectionFormula method, you have to use Crystal formatting.

Why aren't you just using a parameter for this? Create a date range parameter and use this to fill in your dates. Crystal will format the parameter automatically and there is no headache involved. This is a much better solution. If I am going to send a dynamic recordset, I always use the Set DataSource method (and I send a full recordset to the report object). If I only want to restrict certain conditions, I use parameters. Parameters are much more flexible, and easier to use than a recordselectionformula.

Well, to get this to work, you need to break up your date into month, day, and year because that is how Crystal evaluates it. So, I would create a string, and parse it out into the three parts (month, day, and year). Then, use this:



Dim sSQL as string

' I am breaking up the string here so you can see all the parts
'see I have two strings, parsed and within the IN - TO construct

sSQL = "{myTable.Date} In Date(" & sYear & ", " & sMonth & ", " & sDay & ")"
sSQL = sSQL & " To Date(" & EndYear & ", " & EndMonth & ", " & EndDay & ")"

gchwee
04-20-2004, 09:19 PM
OK, this is why I like to use the CRAXDRT, Set DataSource method. With this method you can pass a STANDARD SQL query string as a recordset. With this type of method, a BETWEEN statement will work. Actually, since you are using the native .RecordSelectionFormula method, you have to use Crystal formatting.

Why aren't you just using a parameter for this? Create a date range parameter and use this to fill in your dates. Crystal will format the parameter automatically and there is no headache involved. This is a much better solution. If I am going to send a dynamic recordset, I always use the Set DataSource method (and I send a full recordset to the report object). If I only want to restrict certain conditions, I use parameters. Parameters are much more flexible, and easier to use than a recordselectionformula.

Well, to get this to work, you need to break up your date into month, day, and year because that is how Crystal evaluates it. So, I would create a string, and parse it out into the three parts (month, day, and year). Then, use this:



Dim sSQL as string

' I am breaking up the string here so you can see all the parts
'see I have two strings, parsed and within the IN - TO construct

sSQL = "{myTable.Date} In Date(" & sYear & ", " & sMonth & ", " & sDay & ")"
sSQL = sSQL & " To Date(" & EndYear & ", " & EndMonth & ", " & EndDay & ")"



First of all, I try to run the selectionformula u gave:
sSQL = sSQL & " To Date(" & EndYear & ", " & EndMonth & ", " & EndDay & ")"
ERROR MSG = "(" missing
Never mind, so i try to use the "AND" to combine my "date-range" and "customer name". sSQL as follow:
sSQL = dSQL And "{ChuHuo.Name} ='" & myname & "'"
ERRO MSG = Type dismatch

>.< Cant work...

MKoslof
04-21-2004, 07:32 AM
So troubleshoot it...the syntax is right, maybe I missed a "(" in there. This your project, not mine. That routine will work fine, it has for me and many others :). Have you done any research on this...there are many ways to pass a date range? I suggested parameters, did you even look into this?

You can just pass in a full date, but the variables first must be cast into VALID dates. This will work, another potential method...all of these examples so far will work:




Dim strFormula as string
Dim FromDate as Date
Dim ToDate as Date

FromDate = CDate(Text1.Text) 'cast into a FULL date value first
ToDate = CDate(Text2.Text)

strFormula = "{Table1.Date} in #" & FromDate & "# to #" & ToDate & "#"

Report.RecordselectionFormula = strFormula

gchwee
04-21-2004, 11:55 PM
MKoslof, :) relax. Sure I still remember that is my project, not yours. :)
As for me, forum is a places for many people to discuss and share, izit? :)
I did look at the parameter example you show in other discussion. :)
But my whole brain thinking of selectionformula, we believe the sSQL we assigned should be working, right? :) Why the error message show us that the remaining text is not recognise as "PART" of formula?:) Dont you curious? :)
Recently, I keep on making my research on that(F1 help from the crystal report for native crystal report formula)--> because that is my project, not yours. I notice that "AND" operator in native crystal report is "and" but not "And". But the vb will automatic change it to "And".
YES! And I did notice "SOMETHING" from my research. Kikiki~ :D
In crystal report, the "AND" operator is always "and" but not "And". But in VB, we unable to make the "And" --> "and"(Because it will automatic change to capital letter). Any ideas on how to lock it to small capital letter of "a"nd???? :)
Maybe the sSQL is not recognise the "And" operator. Agree?
Because the both syntax is working.

1) "{Table1.name} = '" & employeename "'" '//correct
2) strFormula = "{Table1.Date} in #" & FromDate & "# to #" & ToDate & "#" '//correct

Why when we link it, it cannot work? I think the problem is "And"--"and". Or I just stupid? Hehe~ nvm
Who got any ideas to lock the "and" operator in vb sSQL automatically change to "And"?????
I wish to try more time before I move to parameter as you suggested.
Just give me have few more times of trying on SelectionFormula, I believe that we almost there.
Regard: KEN

MKoslof
04-22-2004, 07:25 AM
What, "and" and "AND" are the same thing. VB is not case sensitive and neither is Crystal Reports. This has nothing to do with anything. And you say both selection formulas work, but you can't link them? Have you tried this:



strFormula = "{Table1.Name} = '" & employeename & "' AND {Table1.Date} in #" & FromDate & "# to #" & ToDate & "#"



Whether or not the AND is all caps or all lowercase makes absolutely no difference.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum