using a string to get a variable value

[philfer]

Hello,

I have a table with a list of files to import.

some of the source files have names where the dates are in a different format.........some are 20131206 others 131206 etc

I have put the formatname into the table dateformat1, dateformat2

In my code I create strings that represent these formats based on the run date and call them dateformat1, dateformat2 etc

when I try to create a filepath using a recordset building in the name and dateformat field value it puts in the word dateformat1 whereas I want it to find the variable value for dateformat1, dateformat2

I tried using Eval() but with no luck

So if the table has :-

Filename DateFormat
Orders dateformat1
Returns dateformat2


and my code has

dim dateformat1 as string

dateformat1 = year(date()) & month...................

and I want to do filexists(rst("FileName")&rst("DateFormat"))

I am getting C:\Sales DateFormat

Rather than C:\Sales 20131206


Any ideas

Cheers

[SamT]

and I want to do filexists(rst("FileName")&rst("DateFormat"))
("FileName") and ("DateFormat") are Literals. Literally F+i+l+e+N+a+m+e as a string of letters.

Try them without quotes:
filexists(rst(FileName)&rst(DateFormat))

I am not that familiar with SQL, but you might want to try the "Evaluate" expression. Again. no quotes:
filexists(rst([FileName])&rst([DateFormat]))

[philfer]

Hello,

You have to use the field names in inverted commas when referring to a recordset. Besides rst("FileName") returns the actual file name and not the word FileName.

The problem is that the field DateFormat contains strings and my code has variables whose values I want to replace these strings with

Evaluate didnt word sadly

Anyone else!

Cheers

[SamT]

My bad. I missed the digit "1." I guess my guess as to what you had was not a good guess.

so let us clarify things a bit more.

You have two fields in your database, one named "FileName," and the other named "DateFormat."

You have VBA code that looks on the hard drive for a file named after the contents of the FileName field and the DateFormat Field.

What is the purpose of the VBA Variable named dateformat1?

Currently your VBA code assigns the string representing today's date formatted as you specify to the Variable dateformat1.
dateformat1 = year(date()) & month...................

try this VBA code to find your File. I am assuming that rst("FileName") returns the appropriate file name from the Data Base. and rst("DateFormat"), does the same for the Desired date.

dateformat1 = Year(rst("DateFormat")) & Month(rst("DateFormat")) & Day(rst("DateFormat"))
filexists(rst("FileName") & dateformat1 )

My preference when coding for different Domains is to write all code for one Domain, assign the returns to variables as needed, THEN, write all code that references the other Domain, using those variables as needed.

For Example (Pseudocode only, not runnable!):

Dim vbaFilename As String
Dim vbaDate As String
Dim dbDate As String

vbaFileName = GetFromDB("FileName")
dbDate = GetFromDB("DateFormat")

vbaDate = Format(Year(dbDate), "yyyy") & Format(Month(dbDate), mm) & FormatDay(dbDate), "dd")

FileExists(vbaFileName & vbaDate)

[philfer]

Hello,

Thanks for your help

That would work if all the files had the same date format in the title.

The problem is that I have files to find with different date formats

Thats why my table has a date format field for each file name...........so filename 1 could have dateformat1 which represents 131208 and filename2 could have dateformat2 which represents 20131208

Then in my code I create the variables :-

dateformat1= right(year(date()),2) & month..............
dateformat2=right(year(date()),4) & month..............


then I look up the table for the filename and the dateformat this file uses


filexists(rst("FileName")&rst("DateFormat"))


however for the first field I get the actual filename but for the second field I get the word "dateformat1" or "dateformat2"

but I need somehow to get that return from the table to covert into the variable in my code so I end up with :-

orders131208
sales20131208

rather then

ordersdateformat1
salesdateformat2


Hope it makes sense!

Cheers

[SamT]

It might make sense if I did not have to keep jumping from one part of the dialogue to another to determine if a Name means one thing (Database Table Field) or another (VBA Variable,) or if it is actually a Literal and not even a Name.

IMO, your Naming practices are putting way too much responsibility on the letter group "dateformat1."

Confusing?

If xyz("DateFormat") <> "DateFormat1" then
Dateformat1 = xyz("DateFormat1")
Else
DateFormat1 = "DateFormat1"
End If


However, I see the problem. You are assuming that the Return of rst("DateFomat") will "call" the Variable dateformat1, which in turn will return the value right(year(date()),2) & month..............

The solution:

Dim DatePartOfFilename as String
'
'
'
Select Case rst("DateFormat")
Case "dateformat1": DatePartOfFilename = dateformat1
Case "dateformat2"
DatePartOfFilename = dateformat2
End Select

filexists(rst("FileName") & DatePartOfFilename)


Note that the colon in the first Case means NewLine

Edited to correct names in DatePartOfFilename code

[jonh]

Sorry, I can't quite make sense of this thread.

If the dates are always the current date you could just store a normal dateformat string for each record, e.g.
[dateformat] field
yyyymmdd
ddmmyyyy
mmddyyyy

then in a query you simply write
FmtDate: Format(Date(),[dateformat])

which gives you
20131209
09122013
12092013

You can't read variables with eval() but you can make your
own functions and call them (which could then return a variables value if it was in scope)
e.g.

Public function TestFunction as string
TestFunction = "hello world"
end function

test = eval("TestFunction()")

The function you are calling must be public and in a standard module.

[SamT]

As I understand the scenario he has many files whose names consist of an AlphaPart and a DatePart. Unfortunately, the DateParts of the files are not in a consistent format

He has a DB that contains both the AlphaPart of the file name and a String Constant representing the format of the DatePart. I am assuming that the DatePart Value of the name, in any format, is today's date.

The DB Field containing the String Constant is "DateFormat"

The String Constants are "dateformat1," "dateformat2." etc.

In his VBA procedures he has variables named "dateformat1," "dateformat2," etc.

He has been trying to use the String Constant as a Variable since they are spelled the same.

String Constant ("dateformat1") == rst("DateFormat")
Variable (dateformat1) == right(year(date()),2) & month..........

The Select Case above is one way to use a String to select which Variable to use.





Without modifying the DB, I would refactor the code:


Function DatePart(FormatStyle As String) As String
'Uses custom Keyword to return today's date in various formats

Select Case FormatStyle
Case "dateformat1"
DatePart = CStr(Format(Now, "yyyymmdd") 'See also the DateFormat Function
Case "dateformat2"
DatePart = CStr(Format(Now, "yymmdd")
Case Else :Etc.
End Select
End Function

Then use the Function like


filexists(rst("FileName")& DatePart(rst("DateFormat")))





IMO, Jonh's is the best solution. Loop thru the DataBase's DateFormat Field and replace the String Constants with the appropriate Format Strings, ("yyyymmdd", "yymmdd", etc. Then:
filexists(rst("FileName") & Format(Now, rst("DateFormat"))

Note that Date is merely Now formatted to the local display standard. Refomatting Date requires first breaking it down into parts. Using Now allows the direct use of Format in one operation.


TodaysDate = 12092013
A = Right(Date,2) 'Format(Now, "yy")
B = Left(Date,2) 'Format(Now, "mm")
C = Mid(Date,3,2) 'Format(Now, "dd")
A & B & C = Format(Now, "yymmdd")