UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba Code Picking Up Month And Day, But Not Year, Access 2016    
 
   
jriley
post Jun 17 2020, 09:02 AM
Post#1



Posts: 69
Joined: 31-October 02



I have a text box in a form (txtStartDate) that I want to use as a criterion in a vba query, specifically to query against a date field in an ODBC-linked table (tblTransactions2.POST_DATE) to get all records from the table that are >= the date I enter in the text box. Both the text box and the POST_DATE fields are formatted as date fields. When I run the code, the query picks up the month and day as criteria, but not the year. For example, if I enter 5/1/2020, I will get 5/15/2020 and 6/10/2020, but also 8/17/19, 12/1/18, and so on.

I'm using the DateValue function on these because when I don't, I get a type mismatch error. Any assistance would be greatly appreciated. The code is below. Thanks. (NOTE: I realize that the structure of this query is pretty non-standard - there's another thread on it if you're curious as to why it's set up this way. Suffice to say that the network limitations I'm under make it necessary.)

Private Sub Additions()

Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL1 As String
Dim cusipID1 As String
Dim trancode As String
Dim stDt As Date


Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("tblCusip")

stDt = DateValue([Forms]![frmBalance]![txtStartDate])


DoCmd.SetWarnings False
CurrentDb.Execute "Delete * FROM tblDeliver", dbFailOnError

Do While Not rs1.EOF

cusipID1 = rs1!CUSIP.Value


strSQL1 = "INSERT INTO tblDeliver (POST_DATE, ACCT_ID, INSTRUMENT_ID, COST_VALUE, TRAN_CODE, TRAN_DESC_LINE1, TRAN_DESC_LINE2, TRAN_DESC_LINE3) " & _
"SELECT POST_DATE, ACCT_ID, INSTRUMENT_ID, COST_VALUE, TRAN_CODE, TRAN_DESC_LINE1, TRAN_DESC_LINE2, TRAN_DESC_LINE3 " & _
"FROM Transactions_2 " & _
"WHERE Transactions_2.TRAN_CODE = '0025' " & _
"AND DateValue(tblTransactions_2.POST_DATE) >= '" & stDt & "'" & _
"AND Transactions_2.INSTRUMENT_ID = '" & cusipID1 & "';"

db1.Execute strSQL1, dbFailOnError
rs1.MoveNext

Loop
rs1.Close
DoCmd.SetWarnings True

DoCmd.OpenTable "tblDeliver", acViewNormal

End Sub
Go to the top of the page
 
Doug Steele
post Jun 17 2020, 09:13 AM
Post#2


UtterAccess VIP
Posts: 22,305
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Just a wild shot, but see whether changing

CODE
"AND DateValue(tblTransactions_2.POST_DATE) >= '" & stDt & "'" & _

to

CODE
"AND DateValue(tblTransactions_2.POST_DATE) >= '" & Format(stDt, "yyyy\-mm\-dd") & "'" & _

makes any difference.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
Gustav
post Jun 17 2020, 12:19 PM
Post#3


UtterAccess VIP
Posts: 2,272
Joined: 21-February 07
From: Copenhagen


You are (implicitly) converting the date values to text, thus a simple text comparison is done. Don't do that.
Handle as DateTime, thus:

CODE
"AND DateValue(tblTransactions_2.POST_DATE) >= #" & Format(stDt, "yyyy\/mm\/dd") & "# " & _

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 07:58 AM