Search results

It worked. Thanks a bunch. I must be over-thinking things when a simple IIf statement was all I needed.

I need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown. I have an example...

I'm also having trouble creating a crosstab query. Every time I attempt to use the pivot function SQL server declares my alias columns to be invalid. My code is: SELECT PartNumber, manufacturerPartNumber + ': ' + manufacturerName AS [Mfg_Combined], (SELECT Count(*) FROM...

Thanks so much.

This statement works on Access, however it turns out that I also need to replicate this line for a query on MS SQL server. DCount isn't a function on SQL server and the Count function only supports one argument. How can I convert this line into MS SQL server? Mfg_Combined" & DCount("*"...

Okay everything works on Access, however it turns out that I also need to replicate this query on MS SQL server. DCount isn't a function on SQL server and the Count function only supports one argument. How can I convert this line into MS SQL server? Mfg_Combined" & DCount("*", "tblRecords"...

Okay I'll see if I can get it to work with my actual query.

I'm referring to to your db.

I cannot run the cross tab query. It says that Access doesn't recognize A.[ID] as a valid field name.

It doesn't work. It says that Access doesn't recognize A.[ID] as a valid field name.

I can't do that. I don't have enough columns and when I added one just to make it work it gives the previous error of too many headers.

Sorry here it is.

I uploaded an example of what I wanted. I thought a crosstab query would work but, I get an error stating I that I created too many headers.

I need to create a query that strings data horizontally that corresponds to the primary key. Example of what I am looking for Fields: Part_No, Mfg_No, Mfg_Name Query Columns: Part_No, Mfg_Info (Mfg_No: Mfg_Name) Also if the part_no (primary key) has multiple values I need the data to...

I figured it out. It needed quotes because it's a text. Thanks for your help.

Okay. But I still get errors. I decided to drop the date conversion until after I get the IIf statement working. My code is : IIf(OPDT, 2) > 12 OR LEFT (OPDT, 2) = 00, RIGHT (OPDT, 2), LEFT (OPDT, 2) + '/' + '01' + '/' + IIf(LEFT (OPDT, 2) > 12 OR LEFT (OPDT, 2) = 00, LEFT(OPDT,2), RIGHT(OPDT...

I understand that but this is complex. Tried to do this using those and Access gave an error and highlighted END. My code attempt: OPENDATE: CDate(IIf(OPDT, 2) > 12 OR LEFT (OPDT, 2) = 00, RIGHT (OPDT, 2), LEFT (OPDT, 2) END END + '/' + '01' + '/' + IIf(LEFT (OPDT, 2) > 12 OR LEFT (OPDT, 2) =...

I have a query to create in access based off a query already created in SQL The SQL query converts a 4 digit number into a date using this code: CAST(CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN RIGHT(OPDT, 2) ELSE LEFT(OPDT, 2) END + '/' + '01' + '/' + CASE WHEN LEFT(OPDT, 2) > 12...

Thanks a bunch Plog I got it to work.

I got it to work once as a test but accidentally deleted it and now I can't do it again. Crap.