Recent content by mackyrm

Query 1 output, client skills, looks like this CLIENT | SKILL --------- ----------- Joe Bloggs | Psychology Joe Bloggs | Biology Joe Bloggs | Public Speaking Jane Smith | Biology Jane Smith | Biochemistry Jane Smith | Media Jane Smith | Public Speaking...

I have two sets of queries. A client query that lists the client and their skills (a client can have several lines as the skill comes from a child table where the client is in the parent table). I have a job query, that lists employers and the skills that they are looking for (employer = parent...

What I am trying to achieve is the addition of one hour Minty (SQL is taking them one hour back from 00:00:00 to 23:00:00 the previous day, in BST). I can make the 1 hour addition in a query and bring the date back to what it is supposed to be in reporting, but they are not searchable in the...

Thanks Minty. It must be date and time I guess behind the interface for the field, if it recording it as such, but the form is asking only for the date. I suspect the system is defaulting the date time to [date] 00:00:00 (I can't put in the time). In the CRM it is visible as the correct date in...

Hi Minty, Yes, the Access database is read only access. We are only reporting from it, not populating it. The application that uses the SQL Server to populate it, accounts for this time differential. I do not have access to change that application.

Thanks for the reply. If you look at the first post, it provides an example of a contact > product relationship that I recreated in Access (to test in). One to many. The fields are date fields.

Apologies for the delay. What I am trying to do is create a query in Access, linked to an SQL server back end. The date is changed due to BST in SQL Server (I am unable to change how dates are stored in the original database). By one hour. 01/06/2016 in the original application using SQL...

Thanks Minty and Jdraw, here is the output from the debug mysql on the where statement WHERE ( ((Product.Name) Like '*Product*') AND ((DateAdd("h",1,[Product].[startdate]))>=#08/01/2016#) ) (date is greater than 1st August - referencing SQL Server dates and these are in the US date format)

I'm getting a syntax error with single quotes.

Thanks Minty, I will give this try.

Can you query on a query in VBA and filter in the VBA for a cleaned up output? If anyone can advise with a VBA-SQL example, appreciated. Thanks.

Thanks for the reply Minty. I can't even get this working in an access query at the moment connected to SQL Server. The VBA takes into consideration the date formatting and has the # symbols around the dates e.g. mySQL = mySQL + vbCrLf & " (DateAdd(""h"",1,[product].[startdate]))>=#" &...

I would just add that I have tried this in Access (with access tables) with # around the dates and it works correctly, but I am actually connected to a SQL Server backend in my live database, and it doesn't work there. Adding # in the search criteria gives 0 results back.

Hi, I am trying to query on a DateAdd column that I am creating in a query. It is returning data but the dates don't match with the criteria I am trying to pull out of the database. I am getting results on all 'product one' entries. I am running this in a VBA statement to dynamically create...

Thank you for this. Formatting input into the US format before comparison worked.