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.
zimmer9Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
for testing purposes, use this and see what we'll get

ssql = "select * from importMonthlyChecks"

rst.Open ssql, con, 2, 2

debug.print rs!bankname


so we can get the exact value from the field Bankname

or better upload a sample db with some records from the table..
0
 
Rey Obrero (Capricorn1)Commented:
try this

dateFile =mid(rs!bankname, 5,4) & " " & mid(rs!bankname, 11,8)

or

dateFile =Left(Trim(rs!bankname), 4) & " " & mid(rs!bankname, 11,8)
0
 
zimmer9Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.