Solved can't figure where I'm going wrong

[Cliff67]

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]

Wouldn't you want the Year function applied to (tbl_tickets.[DateOpened) ?

 

[theDBguy]

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]

As Gasman asked - Is DateOpened a Date datatype?

Access has Year, Month and Day functions?

What exactly is in DateOpened and CmbYear?

 

[Cliff67]

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]

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]

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?

Edit:
Just tried your way from QBE and get

SELECT AllTransactions.Date
FROM AllTransactions
WHERE (((AllTransactions.Date) Like "*2016"));

No single quotes?

 

[Cliff67]

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

 

[theDBguy]

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:

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]

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.

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

 

[Solo712]

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:

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

Best,
Jiri

 

[CJ_London]

going back to the original post - you are missing a single quote

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

 

[Cliff67]

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]

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]

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]

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?

 

[Cliff67]

yes true

 

[theDBguy]

yes true

There you go. That's the spirit! When you find out the answer, you can update this thread and let us know. Cheers!

 

[The_Doc_Man]

"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]

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