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
3
623 Views
Last Modified: 2013-11-06
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.
0
Comment
Question by:zimmer9
  • 2
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39622309
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
 

Author Comment

by:zimmer9
ID: 39622445
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39622461
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access on Mouse move 5 34
backup programme - VBA 3 24
Binding recordsets to a form 6 26
Runtime Error 9 - Subscript out of Range (Check to see if Sheet Exists) 14 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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