Solved can't figure where I'm going wrong (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 28, 2001
Messages
26,998
If CmbYear is text then that is a PERFECTLY valid approach. Congrats on getting it to work.

Just for clarity, my point was that the fields selected in a combo box are not guaranteed to be text, unlike fields entered through a text box. Fields coming through a combo box are the data type of the field in the underlying query of the .RowSource property.
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
yes I see what you are saying, I use a data source of

Year: Format([DateOpened],"yyyy") of the table Tbl_Tickets grouped together

the data type is a short date so maybe that has been my problem all along. Does it convert it into a text type when formatting?
 

Cronk

Registered User.
Local time
Tomorrow, 00:19
Joined
Jul 4, 2013
Messages
2,770
Nobody suggested what I'd consider to be the simplest solution
Code:
Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets WHERE Year([DateOpened])="  & me.CmbYear )
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 13:19
Joined
Sep 21, 2011
Messages
14,041
Well actually I did, twice, in posts 2 and 10, but it did not get much of a response? :)

Nobody suggested what I'd consider to be the simplest solution
Code:
Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets WHERE Year([DateOpened])="  & me.CmbYear )
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Hi Gasman
I did try your suggestion, I thought I had replied to it, sorry about that.

I gave it a try and for some reason it kept coming back with a type mismatch error, it just would not work for me. But then again I've been having all sorts of problems with my PC recently.

Part of my day job is to test our high speed cameras and their associated software. We use a sentinel HASP protection software to unlock certain functions, everything has been fine until the last update from MS and now I get a failed to load libraries message - weird! never had it before this last update...never mind that is a subject for another forum LOL
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Right Guys,

Moving on from this I managed to get the form to populate where any date that has a TSR/RMA is coloured, now I want to click on the coloured box and open the corresponding records for the date. I have a little quirky problem here. I've posted the code but I don't think it is anything to do with the code.

Sooo...I click on the 2nd Jan 2019 (there is a record for that day and it shows nothing) I click on the 10th Jan 2019 and I get the record for the 1st October 2019, so therefore it is a formatting thing, The under lying table has the date opened field set as medium date (12-Nov-19 style)

any help would be appreciated - below is the full code for the module. it is attached to the click event of the text boxes
Code:
Option Compare Database
Option Explicit

Public Function gridClick()
'This a function that fires when any of the grid text boxes are clicked on the Frm_CalendarYear
Dim ctl As Access.Control
Dim strMonth As String
Dim intCol As String
Dim intMonth As Integer
Dim intDay As Integer
Dim frm As Access.Form
Dim intYear As Integer
Dim selectedDate As Date
Dim stLinkCriteria As String
Dim StDocName As String
  Set ctl = Screen.ActiveControl
  Set frm = ctl.Parent
  strMonth = Replace(Split(ctl.Tag, ";")(0), "txt", "") 'get the month from the text box
  intCol = CInt(Split(ctl.Tag, ";")(1))
  intYear = CInt(frm.CmbYear.Value) 'get the year from the combo box
  intMonth = getIntMonthFromString(strMonth)
  intDay = intCol - getOffset(intYear, intMonth, vbSaturday)
  selectedDate = DateSerial(intYear, intMonth, intDay) 'make the date
  'selectedDate = Format(selectedDate, "dd/mm/yyyy")
  
  'Since you know the date open the form to
  'view the TSR/RMA

If frm.CmbView = 1 Then 'TSR
        stLinkCriteria = "[DateOpened] = #" & selectedDate & "#"
        StDocName = "Frm_Tickets"
        'MsgBox stLinkCriteria ' just checking
ElseIf frm.CmbView = 2 Then 'RMA
        stLinkCriteria = "[DateRaised] = #" & selectedDate & "#"
        StDocName = "Frm_Repair_Main"
        'MsgBox stLinkCriteria
End If

DoCmd.OpenForm StDocName, acNormal, , stLinkCriteria

End Function
Public Function getOffset(intYear As Integer, intMonth As Integer, Optional DayOfWeekStartDate As Long = vbSunday) As Integer
  'If your calendar starts on Sunday and the first day of the month is on a Monday
  'Then everything is shifted one day so label 2 is day one
  'If the first day was Saturday then everything shifts 6 days. So label seven shows 1
  Dim FirstOfMonth As Date
  FirstOfMonth = getFirstOfMonth(intYear, intMonth)
  getOffset = Weekday(FirstOfMonth, DayOfWeekStartDate) - 1
End Function
Public Function getFirstOfMonth(intYear As Integer, intMonth As Integer) As Date
 getFirstOfMonth = DateSerial(intYear, intMonth, 1)
End Function

Public Function getDaysInMonth(FirstDayOfMonth As Date) As Integer
    getDaysInMonth = Day(DateAdd("m", 1, FirstDayOfMonth) - 1)   'Days in month.
End Function
Public Function getIntMonthFromString(strMonth As String) As Integer
  'Assume Jan, Feb..Dec
  getIntMonthFromString = Month("1/" & strMonth & "/2014")
End Function

and I've attached the screen for completion
 

Attachments

  • Calendar View of Technical Support Requests for 2019.pdf
    53.1 KB · Views: 50
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 13:19
Joined
Sep 21, 2011
Messages
14,041
I believe you will need to format the dates in USA format? mm/dd/yyyy.

The clue is the October and January issue.

Try that first, as it is a simple fix.?

HTH
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Hi Gasman

That was my first port of call, it still seems to get confused with anything below the 12th of any given month. I didn't know if there is a setting on the form that would default to US formatting not UK
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:19
Joined
Sep 21, 2011
Messages
14,041
Walk through the code with F8 and breakpoint.
Find out exactly what selectedDate holds.?
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
yep, done that, even put a few msgbox statements to see if it changed and it didn't. The only thing I can think of doing now is Day, Month, year functions, then re-combine it to make sure it is correct. I wonder if the DateSerial function defaults to USA style.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:19
Joined
Sep 21, 2011
Messages
14,041
I wonder if the DateSerial function defaults to USA style.

Well if you had walked through the code, you'd have found out? :confused:

FWIW I do not think it does, and a quick test shows it does not, as you specify each parameter

Code:
? dateserial(2019,11,18)
18/11/2019

However if you format that to 11/18/2019 what happens then.?

HTH
 

vba_php

Forum Troll
Local time
Today, 08:19
Joined
Oct 6, 2019
Messages
2,884
Because LIKE doesn't work so well on numbers.
I don't that's true. the "*" symbol can be used for numbers just like it is used for text. for instance:
Code:
Like "*" & [number], or:
Like [number] & "*", or:
Like [number1] & "*" & [number2]
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Hi Gasman
I don't know but I will check, it might not be today as I've juts had a big pile of work dumped on me and that takes priority over the DB

Cheers, I'll let you know tomorrow or Wednesday
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:19
Joined
Feb 28, 2001
Messages
26,998
There is an inherent flaw in using "LIKE" on a date field that hasn't been translated to text.

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator

From the article, the opening example says it all:

result = string Like pattern

From your first post,

Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets " & _
"WHERE ((tbl_tickets.[DateOpened]) LIKE '*" & CmbYear & ")", dbOpenDynaset)

From a later post,

DateOpened is a date field in a table which stardard dates like 25/11/2018 and CmbYear is a combo box on a form with the current year as the default.

The problem is that LIKE won't work on DateOpened (and the error 13, Type Mismatch, makes perfect sense). But if CmbYear has a value that is any numeric type then you would get an error 13 when using that one, too.

The folks that are telling you to use DatePart are doing so in the hope that you can do an "Equals sign" rather than a LIKE operator. Your loop is probably viable but slower than molasses on a mid-winter day in Montana.

If you have a specific year, you could generate two date-strings from it.

Code:
Dim SOY As String, EOY As String
...
    SOY = "1-Jan-" & CStr( CmbYear )
    EOY = "31-Dec-" & Cstr( CmbYear )
    Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets " & _
        "WHERE tbl_tickets.[DateOpened]) BETWEEN " & _
        "#" & SOY & "# AND #" & EOY & "# ;", dbOpenDynaset)

That would give you a recordset of all tickets opened in the year selected by the combo box. It would also eliminate the problem of type mismatches (Err 13).
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Hi Doc_Man

thanks for that, I realised the same thing a few days ago and got it working in near enough the same manner. I have another form that only shows the selected month with a bit of code using:

Code:
        Set rs = db.OpenRecordset("SELECT * FROM Tbl_Repair_Main " & _
            "WHERE [DateRaised] BETWEEN #" & _
            Format(datFirstDate, "dd mmm yyyy") & _
            "# AND #" & Format(datLastDate, "dd mmm yyyy") & "#", dbOpenDynaset)

so I copied that and added the beginning of the year as 01/01 and the end of the year as 31/12

It was a real Doh moment - I wonder if that is the problem I'm having now that I should format it the same?
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Right...
I walk through the code, if I select 2nd Jan 2019 it selectedDate variable shows 02/01/2019, however the form opens with no record/blank form.
Any date selected in January opens the form as a blank form.

Any date over the 12/13th of the month will open correctly. It is definitely a date format thing, however when I format the selectedDate to use dd/mm/yyyy it still tries to open as a US date system. I'm using Access 2013 and can't find anywhere to set the system date style

I have even built the date up based on strings to see if I could force the format but no luck, :banghead:

any suggestions would be greatly received

Cliff
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:19
Joined
Sep 21, 2011
Messages
14,041
So why not format as mm/dd/yyyy as a test at least? :confused:

In one of my DBs I have

Code:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

and then use it as
Code:
' Set some tempvars for using in sub routines
TempVars("StartDate") = Format(Me.txtDateBW, strcJetDate)
TempVars("EndDate") = Format(Me.txtDateWE, strcJetDate)

Set db = CurrentDb()

SetStatusBar ("Creating payroll records .....")

' SQL for employee recordset
strSQL = "SELECT tblEmployee.EmployeeID, tblEmployee.EndDate, tblEmployee.Forename, tblEmployee.Surname, tblEmployee.Initials, tblEmployee.WeekHours, tblEmployee.BasicRate, tblEmployee.IsCommPaid, tblEmployee.CommMultiLevel  FROM tblEmployee"
'strSql = strSql & " WHERE (((tblEmployee.Payroll)=True) AND ((tblEmployee.EndDate) Is Null))"
'strSql = strSql & " WHERE (((tblEmployee.EndDate) Is Null) AND ((tblEmployee.Payroll)=True)) OR (((tblEmployee.EndDate) Between " & [TempVars]![StartDate] & " And " & [TempVars]![EndDate] & "))"
strSQL = strSQL & " WHERE (((tblEmployee.EndDate) Is Null) AND ((tblEmployee.Payroll)=True)) OR (((tblEmployee.EndDate) > " & [TempVars]![StartDate] & "))"

HTH
 
Last edited:

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Hi Gasman

I tried it the other way round and I get the same result except it doesn't recognise dates over the 12th of the month.

I will give the Public Const a try

many thanks

Cliff
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:19
Joined
Sep 21, 2011
Messages
14,041
Can you post a cut down generic version of your DB.?

We are going around in circles here.

As you say, it is a date issue. It is just being able to get to the bottom of it.?

Hi Gasman

I tried it the other way round and I get the same result except it doesn't recognise dates over the 12th of the month.

I will give the Public Const a try

many thanks

Cliff
 

Cliff67

Registered User.
Local time
Today, 06:19
Joined
Oct 16, 2018
Messages
175
Fixed the problem...
when I looked at what dates were not showing up all of January and half of February would not work and a few days from the previous year.

I looked at the underlying table and found that some of the dates had times attached to them so 02/01/2019 10:32:55. I removed all the times as they are not required, don't know where they come from (likely a Now() function rather than a TODAY() function)

Now the Public Const thing works a treat.

Moral of the story, if you code looks like its working most of the time, check the the table data :mad::eek:
 

Users who are viewing this thread

Top Bottom