By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,256 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,256 IT Pros & Developers. It's quick & easy.

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

P: 98
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!
Sep 22 '06 #1
Share this Question
Share on Google+
6 Replies


P: 98
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
Sep 22 '06 #2

PEB
Expert 100+
P: 1,418
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"

:)
Sep 22 '06 #3

P: 98
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"
Sep 22 '06 #4

PEB
Expert 100+
P: 1,418
PEB
Oppa,
In fact no! Sorry with those codes i've not understand what did you mean!

Expand|Select|Wrap|Line Numbers
  1. Dim TempDate
  2. tempdate=dfirst("LocateDate", "MarketDataTemp","")
  3. docmd.runSQL "INSERT INTO tbl#2 (DateField,NoOfZeros) VALUES (CVDATE('"& str(TempDate) & "'), 60)"
  4.  

:)

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"
Sep 23 '06 #5

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

Expand|Select|Wrap|Line Numbers
  1. Dim TempDate
  2. tempdate=dfirst("LocateDate", "MarketDataTemp","")
  3. docmd.runSQL "INSERT INTO tbl#2 (DateField,NoOfZeros) VALUES (CVDATE('"& str(TempDate) & "'), 60)"
  4.  

:)

If pb here we are...
Thank you so much for your help!! It worked great!
Sep 25 '06 #6

PEB
Expert 100+
P: 1,418
PEB
:)

Have a nice eveneing!

:)
Sep 25 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.