Recent content by gmath

"Date" was pulled out a long time ago by a recommendation by plog "Description"on the other hand wasn't, but I can't see how that would throw an error in this case. Got it, I after some messing around I ended up having to make the statement FYSales1: CCur(Nz([sub_QueryFY].[FYSales],0)) because...

I tried using the Nz function, but I'm guessing it isn't returning a number, but txt. As far as what I'm trying to accomplish, you helped me take my data and break it up into the last 3 fiscal years and current fiscal year. Now I would like to total that data. So I took the qryFYSales query...

I thought the set-up was good to go when I realized, if I use >=GetFiscalYear(DateAdd("yyyy", -3, Date()), then I get multiple lines; however, if I change that to >=GetFiscalYear(DateAdd("yyyy", -0, Date()) then I get singular lines which works great until I try to total the years to get a 4...

namlian - I must say I was a little skeptical of the need for the code at first, but after I had a little more time to think about it I do agree with plog's approach. Even though I will probably end up redoing this database once I have it the way I like it with fixed data samples to branch it...

After some tweaking, I took care of the mismatch. I just ended up changing the GetFiscalYear function to return an integer. The query appears to be working just as you had said it would. Just one more question, and this is probably a simple question that is staring me right in the face, but...

I get a type mismatch in expression. The only thing I can think is that FY_1 is not the same type as FY since the others are the same field.

Ok so I've made the changes recommended, but I must say I'm a little confused. By putting the FY_1: GetFiscalYear([SalesDate])-1, the only thing that happens with the column is it subtracts 1 year from the FY, it doesn't collect all the sales for that fiscal year.

I was considering changing the "date" field name, that was how it pulled out of the spreadsheet I imported so I left it for the time being. It is changed now. Here is the SQL, I've also included a screenshot of the design view. SELECT Sales_History.[Customer Number], Sales_History.Product...

The sales date is the Date field, and if I think i understand what you mean by the signature of my FY function, I called it GetFiscalYear. I've taken a screenshot of both the relationship for the query and the function, hopefully that can clarify.

Sales is located in Sales_History as Sales

Ok so I have a new query FY-SalesHist which has the following fields: Invoice, Product, FY, Date. I have the relationship between the invoice, now what is your next recommended step into obtaining sales by fiscal year?

Sure thing, I'm pulling data from 2 tables: Table: PDZRN1 Fields: ID, Product, Whse, PMgr, PCode, PCat, Description, Unit, Stat, On Hand, On Ord, Unit Cost, Cost Exp, Unit Price, LeadTime, UnitPrcExpDate Table: Sales_History Fields: ID, Invoice, Date, Customer Number, Product, Whse, Qty...

I have spent some time considering your suggestion about creating a fiscal year function, just not sure how to implement it. I can't see how to implement that into the query. I would think to your suggestions I would have a function that set up a fiscal year minus 1 to get the previous, minus...

The reasoning behind thinking short term is the database I'm setting up was suppose to be a quick and dirty database for concept. Once I have the layout of what I think I want and need, I will build it so it updates from other databases. The end result of what I'm looking for is to have a...

I'm making a query that has the following Fields Product (Table PDZRN1) Whse (Table PDZRN1) Description (Table PDZRN1) On Hand (Table PDZRN1) Unit Cost (Table PDZRN1) Unit Price (Table PDZRN1) LastDate: Date (Table Sales_History) (using the Max function) Where I am stuck is I want 4 additional...