Creating a query with a module

[Liz C]

I have created pass-through queries using a function before, but was
wondering how I would go about creating a function/module to create a regular
select query.

Below is the sql view of the query I want it to create:

SELECT VolumeVAS1Q.meter_number, VolumeVAS1Q.MaxOfacct_period_cym AS
acct_period_cym, VolumeVAS1Q.prod_date_time, VolumeVAS1Q.alloc_dth,
VolumeMeas1Q.btu_factor, VolumeMeas1Q.meter_mcf
FROM VolumeMeas1Q INNER JOIN VolumeVAS1Q ON (VolumeMeas1Q.prod_date_time =
VolumeVAS1Q.prod_date_time) AND (VolumeMeas1Q.meter_number =
VolumeVAS1Q.meter_number)
ORDER BY VolumeVAS1Q.meter_number;

Can someone please help me out?

Thank you in advance!

Liz

 

[Piet Linden]

This worked... not pretty, but fairly simple...

Public Function CreateQuery(strQueryName)

CreateQuery = False
Dim qdf As DAO.querydef

Set qdf = DBEngine(0)(0).CreateQueryDef(strQueryName, fSQL)

CurrentDb.QueryDefs.Refresh

CreateQuery = True
End Function

Public Function fSQL() As String
fSQL = "SELECT VolumeVAS1Q.meter_number,
VolumeVAS1Q.MaxOfacct_period_cym AS "
fSQL = fSQL & "acct_period_cym, VolumeVAS1Q.prod_date_time,
VolumeVAS1Q.alloc_dth, "
fSQL = fSQL & "VolumeMeas1Q.btu_factor, VolumeMeas1Q.meter_mcf "
fSQL = fSQL & "FROM VolumeMeas1Q INNER JOIN VolumeVAS1Q ON
(VolumeMeas1Q.prod_date_time = "
fSQL = fSQL & "VolumeVAS1Q.prod_date_time) AND
(VolumeMeas1Q.meter_number = "
fSQL = fSQL & "VolumeVAS1Q.meter_number) "
fSQL = fSQL & "ORDER BY VolumeVAS1Q.meter_number;"
End Function

 

[Liz C]

Where would I put the name I want for the query (VolumeQ)?

Thanks Piet!

 

[Piet Linden]

?CreateQuery("MyQuery")

or if you use just plain DAO (if you can), it would be

Set qdf = DBEngine(0)(0).CreateQueryDef("MyQuery", "SELECT ...")