Excell cell in sql where clause

**cacoyle45** · 05-26-2011, 05:20 AM

hi guys

complete newbie to this - looking to create a pivot table from an mysql db hosted remotely, i have everything working at the moment but i would like to expand on it so that a user can enter a date on the excel sheet and click refresh and the query will use the date range from that cell

eg select * from table1 where date = C1

this is very sloppy but i've got this far

Please Login or Register  to view this content.

any help with this would be greatly appreciated!!! thanks

**Macdave_19** · 05-26-2011, 07:49 AM

Firstly You need to wrap your code!

Secondly looks like your almost there buddy:

Please Login or Register  to view this content.

However i Notice that the field you are apply the where clause to is called "Month" that what format is that in? e.g "February" or "Feb" or "02" or is it a full date? you'll need to format the value from cell C1 to match the field format and if it contaings letters you'll also have to wrap it in apostraphes (however its spelt?)

E.g.

Please Login or Register  to view this content.

Hope this helps?

**cacoyle45** · 05-26-2011, 08:08 AM

thanks for your reply, i did everything as suggested but it keeps coming up with a runtime error 1004?

any idea what the problem is?

**Macdave_19** · 05-26-2011, 08:10 AM

firstly what format is the date field in the sql table?

**cacoyle45** · 05-26-2011, 08:21 AM

i've changed the month field to date field to test the formatting command

this is what i have now

Please Login or Register  to view this content.

i added a message box to test the date output and that is ok, and i also specified the sheet for the date input - but the problem is at the commandtext section for some reason?

**Macdave_19** · 05-26-2011, 08:34 AM

Try This

Please Login or Register  to view this content.

and if that doesn't wor remove the "Array" crap and its brackets then try that

**cacoyle45** · 05-26-2011, 10:59 AM

still no luck runtime 1004 highlighting that commandtext line, do i possibly need more info on the odbc connection somewhere - not sure what the issue is at all

**Macdave_19** · 05-26-2011, 11:03 AM

Its hard to help when idont have the connection lol

once last try:

Please Login or Register  to view this content.

**cacoyle45** · 05-26-2011, 11:08 AM

i added this line to output to a message box to see what the query looks like and it looks fine

Please Login or Register  to view this content.

really stumped! i appreciate you taking the time to help thought this would be straight forward :s

**Macdave_19** · 05-26-2011, 11:12 AM

what about:

Please Login or Register  to view this content.

**cacoyle45** · 05-26-2011, 11:18 AM

same error - it does not like this command text at all lol

**cacoyle45** · 05-27-2011, 04:45 AM

Bump

Anyone have any ideas?

Excell cell in sql where clause

LinkBack

Thread Tools

Rate This Thread

Display

Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Re: Excell cell in sql where clause

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions