SQL Query

JimDandy · Aug 25, 2011

I have an SQL query (under Data/Connections/Properties/Definition) that contains as part of the statement the following line (it’s actually in the query twice, once for “Yes” and once for “No”). In that line is a number ('00477430') that will change and currently, we’re having to edit the query each time we need to change this number but I’d like to have a cell on our main form that they can enter this value in and then perform a refresh:

Is it as simple as entering the page and cell number (and how is that formatted)?

The actual code does not post properly due to some of the characters used, but here is as close as I can get it to post...there should be a "less than" symbol just before the word Body:

WHERE (Journal.CallID='00477430') AND (Journal.EntryText Like '%Body value="YES%')

[FONT=&quot]
[/FONT]

Fazza · Aug 26, 2011

hi,

I wonder what Excel version this is for?

I'm familiar with Excel 2003. In which case code could be something like below, on worksheet change. Modify to suit your real SQL. Untested.

Code:

 const strSQL = "SELECT whatever FROM Journal WHERE Journal.CallID = 'zz'"
 
'having checked that the cell you're interested in has changed
'you might need some error checking too on the cell entry
me.querytables(1).sql = replace$(strSQL,"zz",format$(target.value,"00000000"))
me.querytables(1).refresh

In new versions, the object reference is different; and unfamiliar to me.

Maybe me.listobjects.querytables(1). instead of me.querytables(1).

alternatively, you might be able to enter a ? in the SQL instead of the specific code & set up a parameterised query linked to the particular cell and then it could be automatic without VBA

regards

JimDandy · Aug 26, 2011

Sorry, I'm using Excel 2010....

JimDandy · Aug 26, 2011

Maybe someone can help with this as a resolution instead. If I replace the ‘00477430’ in the query with a question mark, I am prompted for a number. Then, using the Definition screen Parameters button, I can set the value (using the middle selection), and it works. What doesn’t work is if I use the Parameters option to set the cell. The option can be set, but putting the value in the cell I’ve selected does not work. Could it be that my number has leading zero’s in it? I’ve tried setting a custom format on the cell with 00000000, I’ve configured it for Text and neither works. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

JimDandy · Aug 26, 2011

Ok, formatting it as Text did eventually work, not sure why it failed previously but it is working now.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

SQL Query

JimDandy

New Member

Excel Facts

Fazza

MrExcel MVP

JimDandy

New Member

JimDandy

New Member

JimDandy

New Member

Similar threads

Forum statistics

Share this page

SQL Query

JimDandy

New Member

Excel Facts

Fazza

MrExcel MVP

JimDandy

New Member

JimDandy

New Member

JimDandy

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock