Search results

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.

I am trying to generate a query based on dates input by the users in a text box with an input mask, and users will input dd/mm/yyyy. I am using an access front end on an sql server. The server is read only. I am using VBA and DAO to generate the records and query. I am having a problem with...

Will this solution require a way to build relationships via the forms, perhaps using a listbox for eligible clients, selecting them and pressing a button, then doing an update query of sorts??

Yes, this did the trick, thank for that. Now working. :)

Thanks for the reply, appreciated. Is there any reason why it would do this? Isn't concatenating separate strings into one large string just adding up the characters, and ultimately have the same response in the concatenated string? If not, why would that work when the first methods fails?

I am trying to build an sql statement and put it into a string. I can do this, but at character 1024 the text that follows is put on to a new line, cutting a fieldname in two. I have declared the string but perplexed. Any thoughts? the section on vba: mySQL = mySQL + "LEFT JOIN dbo_Contact ON "...