Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to assign a date read from an Access table to a variable in Access 2003 using VBA code?

Posted on 2013-11-04
2
Medium Priority
?
573 Views
Last Modified: 2013-11-04
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 = Right(Trim(rst("bankname")), 8)

However, instead of retrieving the value 10/31/13 in the variable dataFile,
I get the value 12:00:00 AM 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?
0
Comment
Question by:zimmer9
2 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39622189
What is the data type of your dateFile variable?

Also, why aren't you storing this date in a date field in your table?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39622191
dateFile = Left(Trim(bankname), 4) & " " & format(Date(),"mm/dd/yy")
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question