Search results

Like This: Dim strsql As String strsql = "SELECT * " _ & "FROM qryNewestReport " _ & "WHERE 1 = 1 " _ & "AND week >= forms.[employees list].startdate " _ & "AND week <= forms.[employees list].enddate " If Forms![employees list]!department > ""...

I looked it up but it gives no reference to syntax or phrasing. Does it go in the SQL statement and if so how? SelTop(PasID) as ID would not work. I'm somewhat committed to the combo boxes at this point. Thanks Jim

I am using some comboboxes on my main form (frmMain) to filter the records of my unbound subform frm(viewlist). I would like to query all of the records that are filtered at the moment and use them in a a chart. However I can't figure out the SQL. Here is what I have: SELECT...

It worked great! That is exactly what I was trying to do. But I have one question, why does it work? what are the references to ,2,2 and the -1 part? I've never seen that before Thanks again!! Heres is the SQL for others to see: "Q" & format([Week],"q",2,2) & " Week " &...

OK that makes sense, so I should PIvot on Makestring([Week])? In that format on my crosstabquery. How does the query know to look to VBA to get the new field name? My db is a form that allows the user to filter the datasheet in an attched subform (frmcross2). there is also a different subform...

So you are saying that I have to code the SQL statement, not use a regular query, correct? My original question was how to do this in a query not code. The query does not recognize vbMonday, vbFirstDay etc.

Thanks I'm not coding this at the present time though, I'm just using a regular query which will not accept vbMonday etc. Are you implying that this will require coding to complete? Because that is fine with me. Jim

Thanks again but maybe I am not explaining myself clear enough. I want the column names to literaly reflect the Quarter of the year and the 13 weeks that included in that quarter. So if I ran a report for all of 2003 the column names would be: Q1 Week1 Q1 Week2 Q1 Week3 Q1 Week4 Q1 Week5 Q1...

Thanks, but that displayed the following: Week0 , Week1, Week2, Week3 ? I was hoping for Q1 Week1, Q1 Week2 Q2 Week1 Q2 Week2 etc.

I'm trying to display all 52 weeks of the year in the following format "Qtr1 Week1". Here is what I'm using now: PIVOT "Q" & Format([Week],"q") &" " & "Week" &format([Week], "ww") ; But what I need is to have the weeks start at Week1 every quarter. Right now it returns: Q1 Week12 - Q1 Week13...

It's working now Pat: PARAMETERS [forms]![employees list]![startdate] DateTime, [forms]![employees list].[endDate] DateTime; TRANSFORM Max(qryNewestReport.Amounts) AS Amounts SELECT qryNewestReport.Code, qryNewestReport.Function, qryNewestReport.Plan, qryNewestReport.Location...

You know I was going to post this same problem the other day. I found a workaround but, I spent an hour checking syntax, control names and field names to no avail. I actually removed the Trasform part of the query and just ran it as a Select query off the same form fields and it worked? Proving...

Let's tackle your first problem. Anytime you want a user to enter in criteria that they are prompted for you use brackets []. What ever you type in the brackets will be the question or statement displayed to the user in the input box. i.e. Field: ArtworkID Table: tblArtworks Total: Count...

The first thing I do when a form is doing something completely unbelievable is to re-create it. I have seen many forms go corrupt for no reason. Copy all of your controls to a new form and paste them. Check your record source to see if it returns anything when you run it independent of the form...

Dim objOutlook As New Outlook.Application Dim objMail As Outlook.MailItem Dim strsql As String Dim strMail As String Set objMail = objOutlook.CreateItem(olMailItem) objMail.To = Forms.Yourfield objMail.Cc = Forms.Yourfield & "@your address.com" objMail.Attachment = "Your file...

You say that have joined the queries on the Processor field, but then you also say they do not match eachother. How can they be joined if they do not match? What you may want to try is using group headers in a report for both processor fields. This may separate the grouping to where both...

Have you tried using "Max" on the profile field, and then "Group by" on the remaining fields?