Open a record set with a dynamic date

Resolved · Urgent Priority · Version 2016

Wed 28 Aug 2019, 18:53 Delegate Manny said...

Manny has attended:
Access Intermediate course
Access Advanced course

Open a record set with a dynamic date

Hi Guys,

I want to store all the records of a select query in an record set.

Dim rst As DAO.Recordset

ddate = Workday2(Format(Now(), "yyyy-mm-dd"), -18, Holidays("GB"))

Set rst = dbs.OpenRecordset("SELECT tbl_MasterTransactions.HeliumTransID FROM tbl_MasterTransactions WHERE (((tbl_MasterTransactions.ValueDate)=#" & Format(ddate, "yyyy-mm-dd") & "# AND ((tbl_MasterTransactions.[LS])='Ledger'));")

I get the run time error '3075', the syntax error in query expression. not sure what is going wrong here.

Manny

Thu 29 Aug 2019, 10:10 Trainer Jens said...

RE: Open a record set with a dynamic date

Hi Manny,

Thank you for the forum question.

3075 is as you probably already know missing operator in the SQL string. I am pretty sure it has something to do with the use of " instead of '. Unfortunately I am on my annual leave without a PC so I cannot test it. I will come back to you Monday 2nd September.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 2 Sep 2019, 21:00 Trainer Jens said...

RE: Open a record set with a dynamic date

Hi Manny,

I have checked your SQL (the link below)

https://www.eversql.com/sql-syntax-check-validator/

And it returns the error below:

You have an error in your SQL syntax; it seems the error is around: 'AND ((tbl_MasterTransactions.[LS]) = 'Ledger') )' at line 9

I have not been able to change the SQL and remove the error, but it is the SQL string which return the error in your code.

I will look at it again tomorrow evening.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Design View in Relationship window

Your in the Relationship window and changes are required to be made to a Table's design.

Rather than exiting the Relationship window, if you perform a right-mouse click on the table, it opens that table up in Design view

View all Access hints and tips