Select a value of a date field and store as a variable

[ineedahelp]

Can anyone help me with VBA code for selecting the value of a field in a record and storing it as a variable I can then INSERT INTO another table. I will also want to use this variable to rename a file. eg. sample.txt with become sample091506.txt. The info I need to grab is a date that is stored in a field called [LocateDate]. Thanks for any help!

[ineedahelp]

Should I use a SELECT DISTINCT statement? I keep getting Data Type Mismatch because the value I want is a DATE. My Statement reads
Dim TempDate as DATE

SELECT DISTINCT MarketDataTemp.LocateDate AS TempDate FROM MarketDataTemp

[PEB]

Should I use a SELECT DISTINCT statement? I keep getting Data Type Mismatch because the value I want is a DATE. My Statement reads
Dim TempDate as DATE

SELECT DISTINCT MarketDataTemp.LocateDate AS TempDate FROM MarketDataTemp

1. So to have a Global Variable use:

Global TempDate

in a module! If you don't want to be global you can use in your Form

Dim TempDate

2. To set the variable after updating event your field LocateDate:

TempDate=MarketDataTemp.LocateDate

3. In your SQL: SELECT DISTINCT MarketDataTemp.LocateDate AS TempDate FROM MarketDataTemp you need to put ; at the end

4. to create your file name use

[my_file_name]=Mid([my_file_name],1,Len([my_file_name])-4)+TempDate+".txt"

:)

[ineedahelp]

1. So to have a Global Variable use:

Global TempDate

in a module! If you don't want to be global you can use in your Form

Dim TempDate

2. To set the variable after updating event your field LocateDate:

TempDate=MarketDataTemp.LocateDate

3. In your SQL: SELECT DISTINCT MarketDataTemp.LocateDate AS TempDate FROM MarketDataTemp you need to put ; at the end

4. to create your file name use

[my_file_name]=Mid([my_file_name],1,Len([my_file_name])-4)+TempDate+".txt"

:)

Thank you for your help. So that I understand, to obtain the date value from tbl#1 and then add a new record to tbl#2 with this value, I would use the code:

Dim TempDate
SELECT DISTINCT MarketDataTemp.LocateDate AS TempDate FROM MarketDataTemp
docmd.runSQL "INSERT INTO tbl#2 DateField,NoOfZeros VALUES TempDate, 60"

[PEB]

Oppa,
In fact no! Sorry with those codes i've not understand what did you mean!

Dim TempDate
tempdate=dfirst("LocateDate", "MarketDataTemp","")
docmd.runSQL "INSERT INTO tbl#2 (DateField,NoOfZeros) VALUES (CVDATE('"& str(TempDate) & "'), 60)"
 

:)

If pb here we are...

Thank you for your help. So that I understand, to obtain the date value from tbl#1 and then add a new record to tbl#2 with this value, I would use the code:

Dim TempDate
SELECT DISTINCT MarketDataTemp.LocateDate AS TempDate FROM MarketDataTemp
docmd.runSQL "INSERT INTO tbl#2 DateField,NoOfZeros VALUES TempDate, 60"

[ineedahelp]

Oppa,
In fact no! Sorry with those codes i've not understand what did you mean!

Dim TempDate
tempdate=dfirst("LocateDate", "MarketDataTemp","")
docmd.runSQL "INSERT INTO tbl#2 (DateField,NoOfZeros) VALUES (CVDATE('"& str(TempDate) & "'), 60)"
 

:)

If pb here we are...

Thank you so much for your help!! It worked great!

[PEB]

:)

Have a nice eveneing!

:)