Why this sql line return no record found

[Hirasawa]

as self taught beginner, there is a control on form to change date that is textbox "txtstartda te" and "txtenddate ". however when btnnameexport click popup error 3021 showed - no current record.
and also i want to know below line with pointing arrow.

my objective is to create tempquery which filter by date using 2 control on form......tempq uery created then export process run then the tempquery deleted
and im stuck at line ".MoveFirst "

Function Export_To_Excel()

Dim dbs As Database
Dim rst_WrkShtName
Dim WrkShtName As String
Dim qdfTemp As QueryDef


DoCmd.SetWarnings False

Set dbs = CurrentDb

'-- Use this query to find a field to be used as names for the worksheets
Set rst_WrkShtName = dbs.OpenRecordset("SELECT * FROM [qryrcptiddata] WHERE REGDATE Between '*" & txtstartdate & "*' And '*" & txtenddate & "*';")

With rst_WrkShtName

.MoveFirst
Do While Not rst_WrkShtName.EOF
WrkShtName = (rst_WrkShtName("??")) <---- [I]what should i put here.[/I]

With dbs

' Creates a query named as the current Tb_Fieldname, query produces results for each worksheet
' The worksheet is created with the same name as the Query
Set qdfTemp = .CreateQueryDef(WrkShtName, "SELECT * FROM [qryrcptiddata] WHERE * = '" & WrkShtName & "';")

' Transfers this query to a spreadsheet named as WrkShtName in the same file
DoCmd.TransferSpreadsheet 1, 8, WrkShtName, "C:\temp\<Excel_Filename.xls>", True

' Deletes the query after exporting to excel
.QueryDefs.Delete WrkShtName

[twinnyfo]

Line 14 should be:

Set rst_WrkShtName = dbs.OpenRecordset("SELECT * FROM [qryrcptiddata] WHERE REGDATE Between #" & txtstartdate & "# And #" & txtenddate & "#;")

We cannot answer your questoin regarding line 20, as we don't know the field names of your Query. You would put the Field name that you want within the quotes.

Line 26 will never work, as you have an asterisk as the field you are trying to validate in the criteria. You need to have a field name:

Set qdfTemp = .CreateQueryDef(WrkShtName, "SELECT * FROM [qryrcptiddata] WHERE [FieldName] = '" & WrkShtName & "';")

Other than than, you code will produce several errors (and I'm not sure how you got this far without mentioning the numerous erros that will have been created.

Line 16:

With rst_WrkShtName

There is no End With statement.

Line 19:

Do While Not rst_WrkShtName.EOF

There is no Loop statement.

Line 22:

With dbs

Again there is no End With statement.

This is just what I see on the surface.

I think I kinda understand what you are trying to do, but your code has too many error to just focus on the SQL string you created.

[Hirasawa]

seem to be i still novice. sorry about that. now im attaching database for showing thing that i working on. using office 2010. hope i gave more clear view on what i want to do. may need some advice tough..

[twinnyfo]

It's also difficult to understand the need for two separate queries. If you can create a SQL string that works, all you need to do is feed that string into your DoCmd.TransferS preadsheet method, without the need for creating and then deleting a separate QueryDef. This would greatly simplify things.

If you need to name the Spreadsheet based on one of the fields of your Table, then you still need to create a recordset, but if you receive multiple records, I am not sure how that helps.

We typically don't download Access projects (and I can't access them at work, anyway).

[Hirasawa]

mr. twinnyfo thank you for the advice.. if more easy way exist, can you give some example code..that i can work on?

[Hirasawa]

successfully found awesome solution for problem in this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=208583

[zmbd]

Hirasawa
We typically ask that there be one question per thread.