Query Problem

[NetworkGuy101]

I am having a problem getting this query to work. Database type is Access.

<cfquery name="getreport" datasource="FSDB">
SELECT DISTINCTROW (Right(Left([BODY1],13),8)) AS [NewOrder], Min(ITEM_HISTORY.DATE) AS [TranDate] INTO [BookingsTable]
FROM ITEM_HISTORY WHERE (((ITEM_HISTORY.TRANS_ID) Like "COMT*")) GROUP BY (Right(Left([BODY1],13),8)) ORDER BY (Right(Left([BODY1],13),8))
</cfquery>


Return Error is:

[ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Any Ideas?

 

[TruthInSatire]

off the top the error means you're looking for a column that doesn't exist.

when you pass
Like "COMT*"))

to access via CF it means you're looking for a column by that name. use single quotes instead.

also use % as a wild card not *

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[TruthInSatire]

the good ole' "use-access-to-create-the-query-in-design-view-and-copy-the-sql-from-sql-view-to-paste-into-cf" is a good trick for some tougher queries, but you still have to edit the syntax a little.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[r937]

most versions of access don't support % wildcard but do support * wildcard

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

97 and 2000 from CF to access do support %

don't know about the rest.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[NetworkGuy101]

Ok maybe the problem is more involved.
I did a more basic query

<cfquery name="getreport" datasource="FSDB">
SELECT * FROM ITEM_HISTORY
</cfquery>
<cfdump var="#getreport#">

Problem is that the target table is a linked ODBC table. Will coldfusion not work with this?

 

[TruthInSatire]

errors?

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[r937]

sorry, please choose from one of the following two alternatives

1) access 97 does not support % as a wildcard (i just tested it)

2) access 97 does support % as a wildcard and the copy of access that i've been using for seven years has a horrible, horrible flaw that only manifests itself when it detects that it is me who is trying to run a query with a % wildcard, and decides to start messing with my head

can't speak for access 2000 and up, but i seem to recall other people reporting that * works just fine in 2000

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

i pick number 2.

I helped create a huge cart program that still used today by thousands of people world wide. its asp and access 97 as a container (when it started). all queries used % wildcards. most carts now run on 2000, however it started years ago in 97.

i KNOW % and * work in 2k from cf.




If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[r937]

maybe you're thinking of an access project or whatever it's called

here's a page that agrees with me --

http://msdn.microsoft.com/library/d...-us/off2000/html/acconWildcardCharactersS.asp

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[r937]

another page that agrees with me --

http://office.microsoft.com/en-us/assistance/HP051881851033.aspx

notice what site those pages are from?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

obviously we're talking about 2 different things. i'm not talking about within 97. you're right % does not work INSIDE 97.

i'm talking about ASP/CF to access97. my argument is it works from asp or cf. here is a page that proves MY argument.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;162977

to be honest i can't say either way about 97 from coldfusion right now because i can't test it. but it does work with 2k.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[r937]

that page clearly says "an Active Server Pages (ASP) file that was exported from Microsoft Access 97"

i.e. it's no longer an access database, it's an ASP file



by the way, i've created web sites with CF and access, and i have successfully used the * wildcard

probably because i was actually, um, accessing access

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

just like i clearly said ASP.

its asp and access 97 as a container

that's how asp access access db's so the % works in 97 via asp, hand written or access generated.

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[TruthInSatire]

sorry, when i said "that's how" i ment in referance to:

Because ASP files use ActiveX Data Objects (ADO) to communicate with ODBC drivers in order to query the back-end data, the SQL statements they contain have different character requirements than typical Microsoft Access SQL statements. ASP files use the percent sign (%) as a wildcard character in SQL statements whereas Microsoft Access uses the asterisk (*).

If you don't ask the right questions, you don't get the right answers. A question asked in the right way often points to its own answer. Asking questions is the ABC of diagnosis. Only the inquiring mind solves problems.

-Quote by Edward Hodnett

 

[amillia]

NetworkGuy101

1.In your linked table manager in access, you may need to go and specify the whole path.
2.Or Have a seperate dsn for the odbc connected table.