zimmer9
asked on
How to assign a date read from an Access table to a variable in Access 2003 using VBA code?
I am reposting this question because when I tried using the statement
dateFile = Left(Trim(bankname), 4) & " " & Format(Date, "mm/dd/yy")
I get the current date and not the date in the table.
I am developing an Access 2003 application using an MDB type file.
I have a table named importMonthlyChecks from which I read a record and want to assign its date value in format MM/DD/YY to a variable field.
The first value for the field BankName which is a TEXT field with a length of 53 in the table importMonthlyChecks appears as follows:
Starting in position 5 is the value "DATE" and
Starting in postion 11 is the date value "10/31/13".
DATE 10/31/13
-------------------------- ---------- --
I use the followng VBA code:
ssql = "select * from importMonthlyChecks where Left(Trim(bankname), 4) = 'DATE' "
rst.Open ssql, con, 2, 2
dateFile = Left(Trim(bankname), 4) & " " & Format(Date, "mm/dd/yy")
However, instead of retrieving the value 10/31/13 in the variable dataFile,
I get the value of the current date in the variable dateFile?
Do you know how I can get the dateFile to be assigned the value "10/31/13" from the table
importMonthlyChecks?
-----------------
When I try:
ssql = "select * from importMonthlyChecks where Left(Trim(bankname), 4) = 'DATE' "
rst.Open ssql, con, 2, 2
dateFile = rst.Fields("bankname")
I get the value 12:00:00 AM for dateFile.
dateFile = Left(Trim(bankname), 4) & " " & Format(Date, "mm/dd/yy")
I get the current date and not the date in the table.
I am developing an Access 2003 application using an MDB type file.
I have a table named importMonthlyChecks from which I read a record and want to assign its date value in format MM/DD/YY to a variable field.
The first value for the field BankName which is a TEXT field with a length of 53 in the table importMonthlyChecks appears as follows:
Starting in position 5 is the value "DATE" and
Starting in postion 11 is the date value "10/31/13".
DATE 10/31/13
--------------------------
I use the followng VBA code:
ssql = "select * from importMonthlyChecks where Left(Trim(bankname), 4) = 'DATE' "
rst.Open ssql, con, 2, 2
dateFile = Left(Trim(bankname), 4) & " " & Format(Date, "mm/dd/yy")
However, instead of retrieving the value 10/31/13 in the variable dataFile,
I get the value of the current date in the variable dateFile?
Do you know how I can get the dateFile to be assigned the value "10/31/13" from the table
importMonthlyChecks?
-----------------
When I try:
ssql = "select * from importMonthlyChecks where Left(Trim(bankname), 4) = 'DATE' "
rst.Open ssql, con, 2, 2
dateFile = rst.Fields("bankname")
I get the value 12:00:00 AM for dateFile.
ASKER
It seems to me the problem lies with the first SQL statement:
ssql = "select * from importMonthlyChecks where Left(Trim(bankname), 4) = 'DATE' "
rst.Open ssql, con, 2, 2
dateFile = rst.Fields("bankname")
because I get the value 12:00:00 AM for dateFile in the above sequence of statements.
ssql = "select * from importMonthlyChecks where Left(Trim(bankname), 4) = 'DATE' "
rst.Open ssql, con, 2, 2
dateFile = rst.Fields("bankname")
because I get the value 12:00:00 AM for dateFile in the above sequence of statements.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dateFile =mid(rs!bankname, 5,4) & " " & mid(rs!bankname, 11,8)
or
dateFile =Left(Trim(rs!bankname), 4) & " " & mid(rs!bankname, 11,8)