Ok, this is a long one so bear with me. I have a database where the time (for whatever reason) is formatted as a text string. I need to run a query that will show that text string as a time in military format. One way I tried to do this was in a nested IIF statement by analyzing the first 2 left characters and using the Replace function. This worked for the query, but when I went to import data from that query to create a pivot table, that query did not show up. When I deleted the nested IIF statement that used the Replace function, the query showed up. I guess it doesn't like the Replace function for some reason. Here's the nested IIF I was using to replace times of 1:00-8:00 with 13:00-20:00:
wave:IIf(Left([info],2)="1:",Replace([info],"1:","13:"),IIf(Left([info],1)="2",Replace([info],"2:","14:"),IIf(Left([info],1)="3",Replace([info],"3:","15:"),IIf(Left([info],1)="4",Replace([info],"4:","16:"),IIf(Left([info],1)="5",Replace([info],"5:","17:"),IIf(Left([info],1)="6",Replace([info],"6:","18:"),IIf(Left([info],1)="7",Replace([info],"7:","19:"),IIf(Left([info],1)="8",Replace([info],"8:","20:"),[info]))))))))
Again, this works in the query, but the query does not show up as a valid choice when I try to use it for a pivot table. I have been messing around with Format and CDate functions, but no luck so far. If I convert it to show AM/PM it shows the times as 01:00 AM when it should be 01:00 PM.
Really stuck on this, hope someone on here has some insight. Thanks for your help!