Make Table Query Problem 2

[Ulsterman2]

The following query makes a table called "tbl Invoices". The query prompts for the values for "From", "To" and "Threshold". The problem is that when the table is created, the field type for these 3 fields is set as "Binary", which means I can't do any calculations in subsequent queries using these fields. I would like them to be number fields. Any help would be greatly appreciated.


SELECT [tbl Data].ID, [tbl Data].Period, [tbl Data].Type, [tbl Data].Gross, Rnd([Int Ref]) AS Random, [tbl Data].[MUS Ref], [From Period] AS [From], [To Period] AS [To], [HV Threshold] AS Threshold INTO [tbl Invoices]
FROM [tbl Data]
WHERE ((([tbl Data].Period) Between [From Period] And [To Period]) AND (([tbl Data].Type)="AI" Or ([tbl Data].Type)="CRI" Or ([tbl Data].Type)="IC"))
ORDER BY [tbl Data].ID, Rnd([Int Ref]) DESC;

 

[KenReay]

Hi

See

PARAMETERS clause in help

eg something like

PARAMETERS [From Period] Number, [To Period] Number, [Threshold] Number
SELECT [tbl Data].ID, [tbl Data].Period, [tbl Data].Type, [tbl Data].Gross, Rnd([Int Ref]) AS Random, [tbl Data].[MUS Ref], [From Period] AS [From], [To Period] AS [To], [HV Threshold] AS Threshold INTO [tbl Invoices]
FROM [tbl Data]
WHERE ((([tbl Data].Period) Between [From Period] And [To Period]) AND (([tbl Data].Type)="AI" Or ([tbl Data].Type)="CRI" Or ([tbl Data].Type)="IC"))
ORDER BY [tbl Data].ID, Rnd([Int Ref]) DESC;


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ulsterman2]

Hi

I tried the above but it gives an error message - "Syntax error in PARAMETER clause

 

[KenReay]

Hi

have you looked up in help?

I was unsure of exact wording, that is why I said to look it up



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Add a semi-colon ( at the end of the parameters clause:
PARAMETERS [From Period] Number, [To Period] Number, [Threshold] Number[highlight];[/highlight]
SELECT ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KenReay]

Hi

Example

Parameters [MyParam] Integer;
SELECT Table1.Field1, Table1.Field2, [MyParam] As param
FROM Table1;

PARAMETERS Declaration
Declares the name and data type of each parameter in a parameter query.

Syntax
PARAMETERS name datatype [, name datatype [, ...]]

The PARAMETERS declaration has these parts:

Part Description
name The name of the parameter. Assigned to the Name property of the Parameter object and used to identify this parameter in the Parameters collection. You can use name as a string that is displayed in a dialog box while your application runs the query. Use brackets ([ ]) to enclose text that contains spaces or punctuation. For example, [Low price] and [Begin report with which month?] are valid name arguments.
datatype One of the primary Microsoft Jet SQL data types or their synonyms.


Remarks
For queries that you run regularly, you can use a PARAMETERS declaration to create a parameter query. A parameter query can help automate the process of changing query criteria. With a parameter query, your code will need to provide the parameters each time the query is run.

The PARAMETERS declaration is optional but when included precedes any other statement, including SELECT.

If the declaration includes more than one parameter, separate them with commas. The following example includes two parameters:

PARAMETERS [Low price] Currency, [Beginning date] DateTime;


You can use name but not datatype in a WHERE or HAVING clause. The following example expects two parameters to be provided and then applies the criteria to records in the Orders table:

PARAMETERS [Low price] Currency,

[Beginning date] DateTime;

SELECT OrderID, OrderAmount

FROM Orders

WHERE OrderAmount > [Low price]

AND OrderDate >= [Beginning date];


See Also
EXECUTE Statement (Microsoft Jet SQL) SELECT Statement (Microsoft Jet SQL)
HAVING Clause (Microsoft Jet SQL) WHERE Clause (Microsoft Jet SQL)
Microsoft Jet Database Engine SQL Data Types


Example
PARAMETERS Declaration Example


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Ulsterman2]

Working now!

Thanks to you both for your help!