Pass Input Box VBA Variable to SQL Query

**Zoomer36** · 09-14-2011, 03:09 PM

Hello,

I need to prompt the user for a date and then send it to the SQL query I have running in my 2007 Excel VBA. I am using Windows Server 2000 if that makes a difference. I know I can use in input box to get the date from the user and assign it to a variable in VB. My issue is how do I then get it into my SQL script? The script works great now with a date hard coded in. It looks in the tables with 4 joins and gets all the data as I need it.

I would also like to total the figures after I am done. I am struggling with that as I can do it with a macro but I want the report to be done with one push of the button for the user. All I am returning is 2 fields from SQL. An invoice number and a Tax. It can be anywhere from 25 to 125 entries each day. I am willing to total all 125 rows each day if I can't select what is there.

Thanks in Advance for any help.

Charlie

**TMS** · 09-14-2011, 03:13 PM

Might be useful to share the code you have ...

**Zoomer36** · 09-14-2011, 03:17 PM

Thanks. Here is my code.

Please Login or Register  to view this content.

**TMS** · 09-14-2011, 03:20 PM

You need to add code tags as per the forum rules before anyone can provide an answer.

Regards

**TMS** · 09-14-2011, 03:41 PM

I can't test this but I'm guessing it would look something like:

Please Login or Register  to view this content.

Regards

**romperstomper** · 09-14-2011, 03:44 PM

You'll need to format it first, I think:

Please Login or Register  to view this content.

**Zoomer36** · 09-14-2011, 03:53 PM

Hi There!

Thanks! It works like a champ! Really appreciate it.

How about the totaling of the column? I want to put the total in cell C2. This is what I have now from recording a macro

'Figure Totals here and put in cell C2
.Range("C2").Select
.Range("C2").Value = SUM(B2:B125)
.Range("C3").Select

The line where is says Value = SUM(B2:B125) gives me an error. It highlights the colon ":" between the two cell numbers.

**TMS** · 09-14-2011, 03:54 PM

@RS: thought report_date was coming in as a string.

Regards

**romperstomper** · 09-14-2011, 04:15 PM

You are correct - overlooked that. In that case, I suspect:

Please Login or Register  to view this content.

**Zoomer36** · 09-14-2011, 04:19 PM

You both are very good! It actually worked without any formatting at all. I put the formatting in just to be safe and it still works like a champ!

Any hints on getting a total of the column?

Thanks!

Pass Input Box VBA Variable to SQL Query

LinkBack

Thread Tools

Rate This Thread

Display

Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Re: Pass Input Box VBA Variable to SQL Query

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions