Too few parameters. Expected 2 (1 Viewer)

akb · Oct 30, 2018

I've created a query that is pulling data from a table that I created as well as from 4 tables from our software database. The data runs just fine, but when I try to export it to a .txt file I receive the error: Too few parameters. Expected 2. After switching some stuff around I then received the same error, but Expected 3.

Exporting the query data to Excel works fine.

Why is this happening?

I've tried recreating in a new database but end up with the same issue. :banghead:

I appreciate your help!!!!

Below is my SQL statement:
SELECT ("I") AS I, dbo_Customer.Bus_Sold, dbo_Header.Inv_Num, dbo_Header.Grand_Inv_Total, ("RFMS INVOICE") AS RFMS, ("D") AS D, dbo_Header.job_type, Right([dbo_GroupHead.Description],8) AS BG_Num, Nz([BG_Num],[Inv_Num]) AS Job, IIf([Job_Type]=17,(9),IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8))))) AS Job_Type2, ([Job_Type2] & Mid([Job],3)) AS MainJob, (' ') AS Blank, ("000110") AS Debit, dbo_Header.store, Department_Phase.GLDepartment, Department_Phase.CreditGL, Department_Phase.Phase, dbo_Header.Del_Date
FROM (((dbo_Header LEFT JOIN dbo_Customer ON (dbo_Header.Cust_Addr1 = dbo_Customer.Cust_Addr1) AND (dbo_Header.Cust_Name = dbo_Customer.Cust_Name)) LEFT JOIN dbo_GroupDetail ON dbo_Header.SeqNum = dbo_GroupDetail.HeaderSeqNum) LEFT JOIN dbo_GroupHead ON dbo_GroupDetail.GroupHeadSeqNum = dbo_GroupHead.SeqNum) LEFT JOIN Department_Phase ON dbo_Header.Job_Type = Department_Phase.[JobType]
WHERE (((dbo_Header.Inv_Num) Not Like '*cl*') AND ((dbo_Header.Grand_Inv_Total)>0) AND ((dbo_Header.Del_Date) Between [Start Date] And [End Date]));

isladogs · Oct 30, 2018

Try adding date delimiters

Code:

... Between #" & [Start Date] & "# And #" & [End Date] & "#"

GinaWhipp · Oct 30, 2018

If you are exporting the Start Date and End Date must be *available* first which is why I use a Form so the values are available to the query prior to export.

akb · Oct 31, 2018

isladogs - with that code I'm receiving this error: You did not enter the keyword And in the Between...And operator. The correct syntax is as follows: expression [Not] Between value1 And value2.

Thoughts?

akb · Oct 31, 2018

How do I use a form to export the data to a delimited .txt file so it properly recognizes the Chr(13) & Chr(10) formatting?

isladogs · Oct 31, 2018

akb said:
isladogs - with that code I'm receiving this error: You did not enter the keyword And in the Between...And operator. The correct syntax is as follows: expression [Not] Between value1 And value2.

Thoughts?

The expression I wrote is definitely correct so perhaps you copied it incorrectly or something else is wrong in your SQL statement

For info, expressions like ("000110") AS Debit can be simplified to '000110' AS Debit - NOTE use of single quotes

Try pasting in the entire SQL statement below into the SQL view of the query designer

Code:

SELECT 'I' AS I, dbo_Customer.Bus_Sold, dbo_Header.Inv_Num, dbo_Header.Grand_Inv_Total, 'RFMS INVOICE' AS RFMS, 'D' AS D, dbo_Header.job_type, Right([dbo_GroupHead.Description],8) AS BG_Num, Nz([BG_Num],[Inv_Num]) AS Job, IIf([Job_Type]=17,(9),IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8))))) AS Job_Type2, ([Job_Type2] & Mid([Job],3)) AS MainJob, ' ' AS Blank, '000110' AS Debit, dbo_Header.store, Department_Phase.GLDepartment, Department_Phase.CreditGL, Department_Phase.Phase, dbo_Header.Del_Date
FROM (((dbo_Header LEFT JOIN dbo_Customer ON (dbo_Header.Cust_Addr1 = dbo_Customer.Cust_Addr1) AND (dbo_Header.Cust_Name = dbo_Customer.Cust_Name)) LEFT JOIN dbo_GroupDetail ON dbo_Header.SeqNum = dbo_GroupDetail.HeaderSeqNum) LEFT JOIN dbo_GroupHead ON dbo_GroupDetail.GroupHeadSeqNum = dbo_GroupHead.SeqNum) LEFT JOIN Department_Phase ON dbo_Header.Job_Type = Department_Phase.[JobType]
WHERE (((dbo_Header.Inv_Num) Not Like '*cl*') AND ((dbo_Header.Grand_Inv_Total)>0) AND ((dbo_Header.Del_Date) Between #" & [Start Date] & "# And #" & [End Date] & "#"));

Also unless your ODBC table links are prefaced with dbo_, that can be omitted.

Code:

SELECT 'I' AS I, Customer.Bus_Sold, Header.Inv_Num, Header.Grand_Inv_Total, 'RFMS INVOICE' AS RFMS, 'D' AS D, Header.job_type, Right([GroupHead.Description],8) AS BG_Num, Nz([BG_Num],[Inv_Num]) AS Job, IIf([Job_Type]=17,(9),IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8))))) AS Job_Type2, ([Job_Type2] & Mid([Job],3)) AS MainJob, ' ' AS Blank, '000110' AS Debit, Header.store, Department_Phase.GLDepartment, Department_Phase.CreditGL, Department_Phase.Phase, Header.Del_Date
FROM (((Header LEFT JOIN Customer ON (Header.Cust_Addr1 = Customer.Cust_Addr1) AND (Header.Cust_Name = Customer.Cust_Name)) LEFT JOIN GroupDetail ON Header.SeqNum = GroupDetail.HeaderSeqNum) LEFT JOIN GroupHead ON GroupDetail.GroupHeadSeqNum = GroupHead.SeqNum) LEFT JOIN Department_Phase ON Header.Job_Type = Department_Phase.[JobType]
WHERE (((Header.Inv_Num) Not Like '*cl*') AND ((Header.Grand_Inv_Total)>0) AND ((Header.Del_Date) Between #" & [Start Date] & "# And #" & [End Date] & "#"));

I didn't understand your last post so haven't responded to it

akb · Oct 31, 2018

I tried both and I'm still getting that same error. The date that I am filtering on (Del_Date) is the invoice date, so there are records that are empty. Is that causing the issue possibly?

GinaWhipp · Oct 31, 2018

Please see post 3. I use queries all the time to export. That said, what are you doing now to export? Are you just opening to export?

isladogs · Oct 31, 2018

Null values shouldn't be causing problems as these should just be excluded from your results. However you could try:

Code:

...AND ((Header.Del_Date) Is Not Null) And  ((Header.Del_Date) Between #" & [Start Date] & "# And #" & [End Date] & "#"));

Forgot to say, SQL statements requires dates in the format mm/dd/yyyy.
So if you use e.g. dd/mm/yyyy you MUST convert the WHERE condition
e.g.

Code:

... AND ((Header.Del_Date) Between #" & Format([Start Date],"mm/dd/yyyy") & "# And #" & Format([End Date],"mm/dd/yyyy") & "#"));

If the errors recurs, please copy it EXACTLY in your next reply including the error number

akb · Oct 31, 2018

Before I try this...The Del_Date field defaults in format: yyyymmdd

Do I need to convert to mm/dd/yyyy first?

akb · Oct 31, 2018

I tried this - and received syntax error. I'm so confused, I always export data and haven't ran into this problem. What am I missing??

SELECT 'I' AS I, dbo_Customer.Bus_Sold, dbo_Header.Inv_Num, dbo_Header.Grand_Inv_Total, 'RFMS INVOICE' AS RFMS, 'D' AS D, dbo_Header.job_type, Right([dbo_GroupHead.Description],8) AS BG_Num, Nz([BG_Num],[Inv_Num]) AS Job, IIf([Job_Type]=17,(9),IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8))))) AS Job_Type2, ([Job_Type2] & Mid([Job],3)) AS MainJob, ' ' AS Blank, '000110' AS Debit, dbo_Header.store, Department_Phase.GLDepartment, Department_Phase.CreditGL, Department_Phase.Phase, dbo_Header.Del_Date
FROM (((dbo_Header LEFT JOIN dbo_Customer ON (dbo_Header.Cust_Name = dbo_Customer.Cust_Name) AND (dbo_Header.Cust_Addr1 = dbo_Customer.Cust_Addr1)) LEFT JOIN dbo_GroupDetail ON dbo_Header.SeqNum = dbo_GroupDetail.HeaderSeqNum) LEFT JOIN dbo_GroupHead ON dbo_GroupDetail.GroupHeadSeqNum = dbo_GroupHead.SeqNum) LEFT JOIN Department_Phase ON dbo_Header.Job_Type = Department_Phase.[JobType]
WHERE (((dbo_Header.Inv_Num) Not Like '*cl*') AND ((Header.Del_Date) Is Not Null) And ((Header.Del_Date) Between #" & [Start Date] & "# And #" & [End Date] & "#"));

isladogs · Oct 31, 2018

I tried this - and received syntax error.

But you didn't tell me what error! Please help me to help you!
Did it give an error number?
Which section did it highlight as having a syntax error?

Possibly this section where I believe you have an extra ')' shown in RED:
IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8)))))
The brackets MUST be in matching pairs

Go through and check each part of your SQL for other similar errors
Then check the date formats

akb · Nov 2, 2018

The only error I received was syntax error - no number.

I ran the below SQL statement:
SELECT 'I' AS I, dbo_Customer.Bus_Sold, dbo_Header.Inv_Num, dbo_Header.Grand_Inv_Total, 'RFMS INVOICE' AS RFMS, 'D' AS D, dbo_Header.job_type, Right([dbo_GroupHead.Description],8) AS BG_Num, Nz([BG_Num],[Inv_Num]) AS Job, IIf([Job_Type]=17,(9),IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8))))) AS Job_Type2, ([Job_Type2] & Mid([Job],3)) AS MainJob, '' AS Blank, '000110' AS Debit, dbo_Header.store, Department_Phase.GLDepartment, Department_Phase.CreditGL, Department_Phase.Phase, dbo_Header.Del_Date, Format(CDate(Format([InvPrtDate],"0000-00-00")),"mm/dd/yyyy") AS InvoiceDate
FROM (((dbo_Header LEFT JOIN dbo_Customer ON (dbo_Header.Cust_Name = dbo_Customer.Cust_Name) AND (dbo_Header.Cust_Addr1 = dbo_Customer.Cust_Addr1)) LEFT JOIN dbo_GroupDetail ON dbo_Header.SeqNum = dbo_GroupDetail.HeaderSeqNum) LEFT JOIN dbo_GroupHead ON dbo_GroupDetail.GroupHeadSeqNum = dbo_GroupHead.SeqNum) LEFT JOIN Department_Phase ON dbo_Header.Job_Type = Department_Phase.[JobType]
WHERE (((dbo_Header.Inv_Num) Not Like '*cl*') AND ((dbo_Header.Grand_Inv_Total)>0)) AND ((Header.Del_Date) Between #" & Format([Start Date],"mm/dd/yyyy") & "# And #" & Format([End Date],"mm/dd/yyyy") & "#"));

And received the bellow error:
Syntax error in string in query expression '(((dbo_Header.Inv_Num) Not Like '*cl*') AND ((dbo_Header.Grand_Inv_Total)>0) AND ((Header.Del_Date) Between #" & Format [(Start Date], "mm/dd/yyy") & "# And #" & Format ([End Date], "mm/dd/yyyy") & "#"));'.

Question - would it be easier to format the query to run by a date equal to what I enter? So = [Start Date].

Appreciate your help!

isladogs · Nov 3, 2018

1. Please use the code tag button (# in the toolbar above the post/reply window) as it makes it much easier to read long sections of code and fixes an issue where the forum software adds a space after every 50 characters

2. Have you checked the comment made by Gina in posts 3 & 8?

3. I've rechecked and still believe you need to remove the last bracket marked in RED from this section:

Code:

IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8))))[COLOR="Red"][B][SIZE="4"])[/SIZE][/B][/COLOR]

4. I also think there is a bracketing error in the section causing the syntax errors. Again I think you need to remove the bracket marked in RED

Code:

WHERE (((dbo_Header.Inv_Num) Not Like '*cl*') AND ((dbo_Header.Grand_Inv_Total)>0)[COLOR="Red"][SIZE="4"])[/SIZE][/COLOR] AND ((Header.Del_Date) Between #" & Format([Start Date],"mm/dd/yyyy") & "# And #" & Format([End Date],"mm/dd/yyyy") & "#"))

So try the following:

Code:

SELECT 'I' AS I, dbo_Customer.Bus_Sold, dbo_Header.Inv_Num, dbo_Header.Grand_Inv_Total, 'RFMS INVOICE' AS RFMS, 'D' AS D, dbo_Header.job_type, Right([dbo_GroupHead.Description],8) AS BG_Num, Nz([BG_Num],[Inv_Num]) AS Job, IIf([Job_Type]=17,(9),IIf([Job_Type]=18,(8),IIf([Job_Type]=20,(9),IIf([Job_Type]=21,(8)))) AS Job_Type2, ([Job_Type2] & Mid([Job],3)) AS MainJob, '' AS Blank, '000110' AS Debit, dbo_Header.store, Department_Phase.GLDepartment, Department_Phase.CreditGL, Department_Phase.Phase, dbo_Header.Del_Date, Format(CDate(Format([InvPrtDate],"0000-00-00")),"mm/dd/yyyy") AS InvoiceDate
FROM (((dbo_Header LEFT JOIN dbo_Customer ON (dbo_Header.Cust_Name = dbo_Customer.Cust_Name) AND (dbo_Header.Cust_Addr1 = dbo_Customer.Cust_Addr1)) LEFT JOIN dbo_GroupDetail ON dbo_Header.SeqNum = dbo_GroupDetail.HeaderSeqNum) LEFT JOIN dbo_GroupHead ON dbo_GroupDetail.GroupHeadSeqNum = dbo_GroupHead.SeqNum) LEFT JOIN Department_Phase ON dbo_Header.Job_Type = Department_Phase.[JobType]
WHERE (((dbo_Header.Inv_Num) Not Like '*cl*') AND ((dbo_Header.Grand_Inv_Total)>0) AND ((Header.Del_Date) Between #" & Format([Start Date],"mm/dd/yyyy") & "# And #" & Format([End Date],"mm/dd/yyyy") & "#"));

5. If it still triggers an error, suggest you download my SQL to VBA & back again utility available from post #6 in this thread https://www.access-programmers.co.uk/forums/showthread.php?t=293372

GinaWhipp · Nov 3, 2018

The error will continue unless the values are first entered prior to import not during the import. This has been the case for quite some when exporting to Excel.

akb · Nov 5, 2018

I'm still receiving an error.

So to try a different approach... I'm going to do this with a form. Can I export data to a form so it is in this format:

I,ONEI04,HJ249091,10/29/2018,573.00,RFMS INVOICE,249091,,10/29/2018
D,,249091,10/29/2018,573.00,20000110,20102402,2,

The record above is one line... I use Chr(13) & Chr(10) to enter the hard return.

Too few parameters. Expected 2 (1 Viewer)

akb

Registered User.

isladogs

MVP / VIP

GinaWhipp

AWF VIP

akb

Registered User.

akb

Registered User.

isladogs

MVP / VIP

akb

Registered User.

GinaWhipp

AWF VIP

isladogs

MVP / VIP

akb

Registered User.

akb

Registered User.

isladogs

MVP / VIP

akb

Registered User.

isladogs

MVP / VIP

GinaWhipp

AWF VIP

akb

Registered User.

Similar threads

Users who are viewing this thread