Excel - Querytable - SQL

[Mitch_s_s]

Hi,

I am attempting to create a querytable, as soon as a workbook is opened.

I have had a bit of a nightmare with the Syntax for the SQL.

I am attempted to pull all records from a table, that were received between 07:00:00 and 20:00:00 "yesterday".

Could someone please provide me with some sample code, that would do this for me.
I've tried tried passing the dates as variables in my vb module, but had no luck!

Any help would be appreciated!!

Mitch

[si_the_geek]

Assuming you have a Where clause, this condition will check for "between 07:00:00 and 20:00:00 "yesterday" ":

DateField Between (DateAdd('d', -1, Date) + #7:00:00#)  And  (DateAdd('d', -1, Date) + #20:00:00#)

...I'm not entirely sure if DateAdd is apt for this, but it hopefully will be (it is supported by Jet, which I presume is what is used).

[Mitch_s_s]

Spot on Si!

Not in work till Thursday now, so I'll check it out then and will let you know!

Thanks!

Mitch

[Mitch_s_s]

This is the string I've got when my code runs

can see you what the error is?

It's connects to an access database, my SQL isn't too hot!

Is there any where I could learn more advanced stuff like this?

SELECT * FROM PRDADMIN_EVENT WHERE (((PRDADMIN_EVENT.EVENTTYPEID)="RNDZ") AND ((PRDADMIN_EVENT.DATEEVENT) Between (DateAdd('d', -1, Date) + #7:00:00#)  And  (DateAdd('d', -1, Date) + #20:00:00#) AND ((PRDADMIN_EVENT.DESCRIPTION)="Reply Received - Outstanding = 0"));

[si_the_geek]

I can't see what the error is, because only you have that information.

All I know is that you've got an SQL statement that looks valid - I have no idea if it is giving you an actual error message, or if it is returning no data, or if it is returning the wrong data, or something else entirely.

[Mitch_s_s]

My bad!

It says "SQL Syntax Error"

But I can't see what the problem could be!

new to this side of things!!

[elleg]

Where do you have your SQL? Is it saved in a variable within a VBA module, or is it just a query in MS Query, or... ?

Also, as for the syntax of your SQL, I think you have some extra parenthesis and some misplaced parenthesis.

I think your SQL should probably be:

SELECT	* 
FROM	PRDADMIN_EVENT
WHERE	(PRDADMIN_EVENT.EVENTTYPEID	="RNDZ") 							AND 
	(PRDADMIN_EVENT.DESCRIPTION	="Reply Received - Outstanding = 0")				AND
	(
		PRDADMIN_EVENT.DATEEVENT	BETWEEN (DateAdd('d', -1, Date) + #7:00:00#)	AND
							(DateAdd('d', -1, Date) + #20:00:00#) 
	);

Or, in one line:

SELECT * FROM PRDADMIN_EVENT WHERE (PRDADMIN_EVENT.EVENTTYPEID	="RNDZ") AND (PRDADMIN_EVENT.DESCRIPTION="Reply Received - Outstanding = 0") AND (PRDADMIN_EVENT.DATEEVENT BETWEEN (DateAdd('d', -1, Date) + #7:00:00#) AND (DateAdd('d', -1, Date) + #20:00:00#));