I am totally stuck. I have a query
Works perfectly if I manually install the tail number "N4615W" and the disc_date. The problem is I need both of those items to be variables that I pull from a form. After trying without success to code the query language into the vba code I gave up and opted to try dcount.
The goal is to count the number of records for the tail number that fall within a specificed month and date range. On the form I have a Discrepancy Date, this is a mm/dd/yyyy format. I am trying to extract the month and date for the query and match that with what is in the table.
The following code is test code only. Proving code to make sure that I am getting the results that I want before I put it to the final project.
The second cnt definition is where I am getting stuck at, the first cnt definition is commented out so I can retain the code for later clean up. I know it has something to do with formatting the month and year out of the txtdate field. The param variable is the comparison that I am trying bump against to find the matches.
This is one of the last two elements that I need to put a key function into my program. Any help is appreciated.
Code:
SELECT Count(tbl_Log_Pages.Logpg) AS CountOfLogpg, tbl_Log_Pages.tail, Format([disc_date],"mm"", ""yyyy") AS Expr1
FROM tbl_Log_Pages
GROUP BY tbl_Log_Pages.tail, Format([disc_date],"mm"", ""yyyy")
HAVING (((tbl_Log_Pages.tail)="N4615W") AND ((Format([disc_date],"mm"", ""yyyy"))="07, 2018"));
Works perfectly if I manually install the tail number "N4615W" and the disc_date. The problem is I need both of those items to be variables that I pull from a form. After trying without success to code the query language into the vba code I gave up and opted to try dcount.
The goal is to count the number of records for the tail number that fall within a specificed month and date range. On the form I have a Discrepancy Date, this is a mm/dd/yyyy format. I am trying to extract the month and date for the query and match that with what is in the table.
The following code is test code only. Proving code to make sure that I am getting the results that I want before I put it to the final project.
Code:
Private Sub txtdate_AfterUpdate()
'Set Dims
Dim c As String 'ATA Code
Dim d As Date 'Date of Discrepancy
Dim t As String 'Grab the tail number for the query
Dim q As String 'Two digit month in the date
Dim w As String '4 digit year in the date.
Dim param As String 'this is the total of m & y so that the info can be passed to the query
Dim cnt As String
Dim myyr As String 'Two digit year extracted from the Date
Dim myday As String 'Three digit year extracted from the date
c = Me.txtata.Value
d = Me.txtdate.Value
t = Me.txttail.Value
q = Format(d, "mm")
w = Format(d, "yyyy")
param = q & "," & w
'cnt = DCount("[Logpg]", "tbl_Log_Pages", "[tail] = '" & t & "'") 'We know this code works. Need to experiment with the date functions
cnt = DCount("[Logpg]", "tbl_Log_Pages", "[disc_date] = '" & param & "'")
Me.txtcnt = cnt
myyr = Format(d, "yy") 'Two Digit year format.
myday = Format(Format(d, "y"), "000") 'Three digit day of the year.
Me.txtyr.Value = myyr
Me.txtdy.Value = myday
Me.txtmonth = q 'Display just the month number from the date
Me.txtyear = w 'Display just the 4 digit year from the date
Me.txtnext = cnt + 1
Me.txtout.Value = c & myyr & myday 'This string will build the log page numbers for me
End Sub
The second cnt definition is where I am getting stuck at, the first cnt definition is commented out so I can retain the code for later clean up. I know it has something to do with formatting the month and year out of the txtdate field. The param variable is the comparison that I am trying bump against to find the matches.
This is one of the last two elements that I need to put a key function into my program. Any help is appreciated.