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

Cliff67

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 16, 2018
Messages
175
Hi Guys

I'm trying to set a recordset as below

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

But I keep getting a run time error of 3075 which means I've got the syntax wrong but can't see where I'm going wrong

Thanks in advance :banghead:
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:43
Joined
Sep 21, 2011
Messages
14,048
Wouldn't you want the Year function applied to (tbl_tickets.[DateOpened) ?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:43
Joined
Oct 29, 2018
Messages
21,358
Hi. Try to paste your SQL statement into the query designer to see the error again and maybe get a clue on the problem.
 

jdraw

Super Moderator
Staff member
Local time
Today, 00:43
Joined
Jan 23, 2006
Messages
15,364
As Gasman asked - Is DateOpened a Date datatype?

Access has Year, Month and Day functions?

What exactly is in DateOpened and CmbYear?
 

Cliff67

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 16, 2018
Messages
175
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.

I want to use this code to select all the table entries for the year that they were opened via the Combo box year. I am using it to populate a form
 

Cliff67

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 16, 2018
Messages
175
DBguy just tried the copy and paste but it has to be altered so much I can't get it working in the VBA screen. I have to reference the forms!frm_CalenderYear!CmbYear but that worked ok but I cant get it working. maybe I will ref the form value
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:43
Joined
Sep 21, 2011
Messages
14,048
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.

I want to use this code to select all the table entries for the year that they were opened via the Combo box year. I am using it to populate a form

So wouldn't you want to compare year against year? :confused:

Edit:
Just tried your way from QBE and get
Code:
SELECT AllTransactions.Date
FROM AllTransactions
WHERE (((AllTransactions.Date) Like "*2016"));

No single quotes?
 

Cliff67

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 16, 2018
Messages
175
Hi Gasman

The DateOpened Field has short dates in them going back to 2014, so I want to pull out using VBA then I can populate the form as shown attached with just a green backcolour to indicate that the tech support call has been received
 

Attachments

  • YearView.jpg
    YearView.jpg
    97.5 KB · Views: 63

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:43
Joined
Oct 29, 2018
Messages
21,358
DBguy just tried the copy and paste but it has to be altered so much I can't get it working in the VBA screen. I have to reference the forms!frm_CalenderYear!CmbYear but that worked ok but I cant get it working. maybe I will ref the form value
Okay, try it this way then:
Code:
Dim strSQL As String

strSQL = "SELECT * FROM..."
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL,...
Copy and paste the result of Debug.Print strSQL in the Immediate Window to the query designer.
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:43
Joined
Sep 21, 2011
Messages
14,048
Hi Gasman

The DateOpened Field has short dates in them going back to 2014, so I want to pull out using VBA then I can populate the form as shown attached with just a green backcolour to indicate that the tech support call has been received

Perhaps, but your way works IF you get the syntax correct, as I tested above. I was not sure that would work for dates, but it does.:eek:

However I still think I would prefer to go with YEAR(field)=cmbYear even if it means using a function.
 

Solo712

Registered User.
Local time
Today, 00:43
Joined
Oct 19, 2012
Messages
828
However I still think I would prefer to go with YEAR(field)=cmbYear even if it means using a function.

Yes, I think Gasman is right and the code below should do it:

Code:
Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets " & _
"WHERE DatePart("yyyy",DateOpened) = " & CmbYear, dbOpenDynaset)


Best,
Jiri
 

CJ_London

Super Moderator
Staff member
Local time
Today, 04:43
Joined
Feb 19, 2013
Messages
16,553
going back to the original post - you are missing a single quote

"WHERE ((tbl_tickets.[DateOpened]) LIKE '*" & CmbYear & "')", dbOpenDynaset)
 

Cliff67

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 16, 2018
Messages
175
Hi all
Just a quick update. no matter what I do I get error 13 type mismatch so I've done a monstrous hack (tm) and I know you will all be appalled at me for this but...

I've opened the full table without any WHERE clause then I'm looping through the table setting a variable as DatePart("yyyy", rs!DateOpened)
then I check if the variable is the same as the combo box selecting the year.

It's not the nicest way and it IS a very big step backwards in my programming skills but it is working now
many thanks to everyone
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:43
Joined
Oct 29, 2018
Messages
21,358
Hi all
Just a quick update. no matter what I do I get error 13 type mismatch so I've done a monstrous hack (tm) and I know you will all be appalled at me for this but...

I've opened the full table without any WHERE clause then I'm looping through the table setting a variable as DatePart("yyyy", rs!DateOpened)
then I check if the variable is the same as the combo box selecting the year.

It's not the nicest way and it IS a very big step backwards in my programming skills but it is working now
many thanks to everyone
Wow! That's intense. Well, as long as it works, I guess. Cheers!
 

Cliff67

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 16, 2018
Messages
175
DBguy I agree, it feels like I'm giving up on doing it correctly but it does work just to show the Technical Support Requests and the issue of RMAs
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:43
Joined
Oct 29, 2018
Messages
21,358
DBguy I agree, it feels like I'm giving up on doing it correctly but it does work just to show the Technical Support Requests and the issue of RMAs
I wouldn't say "giving up" but merely making it work while waiting to figure out a better way. Right?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:43
Joined
Feb 28, 2001
Messages
27,001
"WHERE ((tbl_tickets.[DateOpened]) LIKE '*" & CmbYear & ")", dbOpenDynaset)

My question is what is the data type of CmbYear? Is that text? Or is it a combo box, in which case what is the data type of the underlying first column? Because LIKE doesn't work so well on numbers.
 

Cliff67

Registered User.
Local time
Yesterday, 21:43
Joined
Oct 16, 2018
Messages
175
Doc_Man

CmbYear is a combo box which queries the main table for entered years so I'm not selecting a year that doesn't have any entries...Anyway I finally cracked it

Set rs = db.OpenRecordset("SELECT * FROM Tbl_Tickets " & _
"WHERE [DateOpened] BETWEEN #01/01/" & CmbYear & _
"# AND #31/12/" & CmbYear & "#", dbOpenDynaset)

I realised I was looking for entries that are from 1st Jan until the 31st Dec every year so this format was used in a month view calendar I have

many thanks to all those who have helped me, much appreciated

Cliff
 

Users who are viewing this thread

Top Bottom