Passing larger than 255 SQL string 1

[Darrylles]

Hi all,

Summary: I've produced a form that allows users to easily select criteria via combos on a form which I can use to build a SQL string.

I'm basically using a SQL string that selects everything and then plonking WHERE clauses on the end dependant on user selections from those combos.

I then want to pass this string to a form that will use the string as it's recordsource and display the data with the user selected criteria.

Problem is, the string is larger than 255 chars (a string being restricted to 255).

I'm using the standard wizard to create the button to run the form and then editing it so that stLinkCriteria passes the SQL string over (partially at the moment).

Any way of getting around this?

My only idea is creating a query object at runtime - but is this possible?

Could I use a memo data-type even?

Any pointers appreciated; solution gets as many stars as I can give 'em ;-)

Thanks all and ATB,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[jrbarnett]

Darryle,

Creating a Query object at runtime is certainly possible, as is editing an existing one (but you then get involved with string parsing).

John

 

[Darrylles]

John,

Thanks for the runtime Query object confirmation.

However, how do I manage larger than 255 chars per string, if a string var holds only 255 chars?

I can handle string-manipulation (dependant on the complexity and the fact that I've only got 24 hours per day ;-)), but I AM hoping that simple end-of-string concats of WHERE clauses will cover it.

If it gets more complicated - I'll attempt it and look for a new method if it's looking inefficient for the time spent producing a user-friendly 'criteria' form.

A way around the 255 char per string limit is still my bug-bear (how do I manipulate a 300 char string (without arrays and pointers pleeeez!!!)).

There must be a way of doing this, because Access queries easily hold more than 255 chars. How do I reference those SQL strings?

Kind Regards

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Bullsandbears123]

A string in VB can be more then 255 (I think) I have rather long and complex sql strings that I use similar to your way. Now, if you put your sql string in a field that is limited to 255. The way to change that is open the table, go to design view and change text to memo. You should have alot more space to insert your string.

Another option is to have more then one query changed, which I also use. Make a query that incorporates all you need call it "qry1". Then make "qry2" that pulls information from "qry1". You can again make another query, "qry3" that pulls from "qry2". Then you can run your first criteria in qry1, your second critera in qry2 and what ever else in qry3 or qry4 etc etc. This is a long way to do it, but I use it this way b/c my DB is really complex. Your best bet is to find out exactly where the problem is, my guess is it's most likely not the string.

Another why to do it is to reference the form directly in your SQL string

Example:
Select [field1],[field2] FROM table1 Where field1=form1!txtbox

Hope this helps
Can you post the code your having problems with?

 

[cmmrfrds]

You can put the sql into multiple strings.
Dim sql1, sql2, sql3 as string

'- build the sql strings
sql1 = "Select something up to 255"
sql2 = "more sql statement"
sql3 = "where etc"

Me.Recordsource = sql1 & sql2 & sql3

 

[Darrylles]

Hi,

Sry B&B, but I'm building dynamic SQL - not producing Access queries. I'm doing this in VBA (I hope that I AM in the VBA coding forum!).

I want to create a query-string at runtime that I can then apply to a single form as it's recordsource that will pull back data dependant on what criteria the user wants to see.

I want to do this to get away from creating multiple MS Access design-time queries.

My problem is that the 'string' data-type is 0-255, and a variable that I define as string would strip a lot of my SQL string (it may be 400 chars).

There is NO memo type to use as a variable in VBA.

In VB or C I'd use an array and pointers. Is there no easier way in Access via VBA?

Should I produce a 'dummy' table with a single memo field, paint it on my form that 'holds' the sql string and then build the string within the memo field?
Seems a bit convoluted to me.

There HAS to be a way of doing this - it MUST have been done (it has, Access does it).

I could use the 'default' base Access query and pass parameters to it from the form, but then you get the problem of some or most fields being passed nulls because the user isn't interested in that particular field and hasn't selected anything.

I think that perhaps there is a solution in what you say B&B, but I'm not clear on how to apply it (especially without multiple design-time Access queries).

Hopefully,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Darrylles]

CMM,

That's the one.
I knew there was something; I could 'taste' it.
3 hours later - I can get on.

Much appreciated - both of you.

Sry B&B, for my question - CMM's fits the bill exactly.

Kind regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[swaybright]

I have done a similar thing using a search form and a query. By constructing a form with the desired unbound fields and a query that contains the desired table fields, I entered the following into the where clause:
(((IIf(IsNull([forms]![nameofsearchform]![nameofunboundcontrol]),&quot;&quot;,[nameoftable].[nameofcontrol]=[forms]![nameofsearchform]![nameofunboundcontrol]))<>False) AND . . . .
you can string as many of these as you have controls separated by AND (I have over 30!). Now just put a button on your search form that opens a viewing form using your query as the data source.

Not as elegant as cmmrfrds method, but it works.

Regards

 

[Darrylles]

Hi again,

Sway, yours is actually IDENTICAL to CMM's.

He uses defined variables - you use unbound textboxes.

Unbound textboxes have individual names - these are really variables.

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

The reason that I came back to this, is that I implemented CMM's solution, via Global variables defined in a module.

I had a problem where the global vars weren't retaining the values when I looked at them on another form.
I got no errors at all, but on the 2nd form they were always empty or 'null'.

The reason? I named 'em SQL1, SQL2, SQL3 etc, and it seems that Access reserves these names (possibly for internal use).

I'm assuming this because as soon as I redefined them as SQLsub1, SQLsub2, SQLsub3 it worked perfectly.

Strange?

I'll add it as a discussion anyway.

Regards & thnx to all,

Darrylle









&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk