Export Not the same as Query 2

[vise]

Hi,
Having a spot of trouble here. I have a query that uses a parameters from a form to export. The query runs fine and gathers only the respective data. When I try and conduct the export it gathers other data points that it shouldn't. It's using the same query when conducting the export.. They should be the same right?
TIA
-sin

 

[Remou]

Could this be due to date formats?

 

[vise]

The parameters it limits the query by are doubles and made sure they are by using cdbl(). No dates used

 

[Remou]

Best post the SQL, I suppose.

 

[vise]

It's a bit messy so please excuse.
The query works perfectly in Access, just when the query executes through DAO for the export, it adds on data points beyond the radius..
The three parameters that I set are: forms!frmrapidresponse!latitude.value, forms!frmrapidresponse!longitude.value, forms!frmrapidresponse!txtradius.value

-----

SELECT [Standard Info LA].InsuredName, IIf([Latitude] Is Not Null And [Longitude] Is Not Null And forms!frmRapidResponse!Latitude.value Is Not Null And forms!frmRapidResponse!Longitude.value Is Not Null,Format(Distance([Latitude],[Longitude],forms!frmRapidResponse!Latitude.value,forms!frmRapidResponse!Longitude.value),"0.0#"),99999) AS [Distance from Fire], [Standard Info LA].[WPU Zone], [Standard Info LA].[Truck GPS Tag], [Standard Info LA].[Pol Num], [Standard Info LA].Street, [Standard Info LA].City, [Standard Info LA].State, [Standard Info LA].Zip, [Standard Info LA].[Emergency Person], [Standard Info LA].[Emergency Number 1], [Standard Info LA].[Emergency Number 2], [Standard Info LA].[Contact Procedure], [Standard Info LA].Latitude, [Standard Info LA].Longitude, [Standard Info LA].Elevation, Agent.[Agency Name], [Standard Info LA].[Agent Name], [Standard Info LA].[Agent Phone Direct], Agent.[Phone Number], [Standard Info LA].[Agent Email], [Standard Info LA].[Rapid Response Enrolled], [Standard Info LA].[Access/Gate Code], CDbl(IIf([Latitude] Is Not Null And [Longitude] Is Not Null And [forms]![frmRapidResponse]![Latitude].[value] Is Not Null And [forms]![frmRapidResponse]![Longitude].[value] Is Not Null,Distance([Latitude],[Longitude],[forms]![frmRapidResponse]![Latitude].[value],[forms]![frmRapidResponse]![Longitude].[value]),99999)) AS [Sorting Order]
FROM Agent RIGHT JOIN [Standard Info LA] ON Agent.[Agent ID] = [Standard Info LA].[Agent ID]
WHERE ((([Standard Info LA].[Rapid Response Enrolled])=No) AND ((CDbl(IIf([Latitude] Is Not Null And [Longitude] Is Not Null And [forms]![frmRapidResponse]![Latitude].[value] Is Not Null And [forms]![frmRapidResponse]![Longitude].[value] Is Not Null,Distance([Latitude],[Longitude],[forms]![frmRapidResponse]![Latitude].[value],[forms]![frmRapidResponse]![Longitude].[value]),99999)))<=[forms]![frmrapidresponse]![txtradius].[value]))
ORDER BY CDbl(IIf([Latitude] Is Not Null And [Longitude] Is Not Null And [forms]![frmRapidResponse]![Latitude].[value] Is Not Null And [forms]![frmRapidResponse]![Longitude].[value] Is Not Null,Distance([Latitude],[Longitude],[forms]![frmRapidResponse]![Latitude].[value],[forms]![frmRapidResponse]![Longitude].[value]),99999));

 

[PHV]

when the query executes through DAO[/!]
Hou have to play with the Parameters collection (and probably the Eval function) before opening the recordset.

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

 

[vise]

Here's an interesting thing. If I just use Docmd.outputto instea of DAO to export it does it perfectly. However, I need to edit some items on the sheet before saving it. Is there a way to intercept that sheet and edit it?
-- This might be easier than trying to figure out why DAO is causing extra records to appear.

 

[Remou]

I am not quite sure that I get your point. you can use a query for a recordset ...
I think, perhaps, PHV is suggesting you use parameters which could be set from all those Form thingies.

 

[vise]

I just used Parameters and replaced all the references to the form. Why won't it execute the reference to the main form.. so strange. Any hoot, thanks PHV and Remou, had a tough time seeing through the clouds