DCount Formating a date

[Thumper75]

I am totally stuck. I have a query

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.

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.

 

[pbaldy]

Well, this function alone appears to return the desired format:

?format(date(),"mm, yyyy")
10, 2018

 

[Thumper75]

pbaldy, that code looks great but how do I incorperate that into my Dcount statement?

cnt = DCount("[Logpg]", "tbl_Log_Pages", "[disc_date] = #" & q & "/" & " " & "/" & w & "#")

in the form txtdate is formatted to mm/dd/yyyy. In the table the disc_date is formatted to mm/dd/yyyy, hence the need to look for records with the same month and year in the table as in the form. Basically I'm trying to omit the day of the month.

 

[Galaxiom]

I am totally stuck. I have a query

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"));

Not a very good query. HAVING is applied after GROUP BY so the conditions should be in a WHERE clause. Your query is grouping records that you don't even want returned.

You are also applying a function to every record before they can be selected. The BETWEEN can use the index to select only the wanted records.

SELECT Count(tbl_Log_Pages.Logpg) AS CountOfLogpg, tbl_Log_Pages.tail, Format([disc_date],"mm"", ""yyyy") AS Expr1
FROM tbl_Log_Pages

WHERE (((tbl_Log_Pages.tail)="N4615W") 
AND 
[disc_date] BETWEEN #7/1/2018# AND #7/31/2018#

GROUP BY tbl_Log_Pages.tail, Format([disc_date],"mm"", ""yyyy")
 ;

The dates can come directly from a form or be derived using Month(), Year() and DateSerial().

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.

Post your attempt and we can explain what you were doing wrong.

The function would be a very inefficient solution.