Search results

Have been unable to find any solutions for this in the forum or the help files in Access. Project has a [cutindate] and an [enddate]. A [flatamount] ($$ savings amount]. I am calculating the month savings by dividing the [flatamount] by the number of months between [cutindate] and [enddate]...

Thanks a lot, I will try that.

Looked through DateSerial but possibly not the way to go. I have a text field, "Month", with the dates in this format: Jan-06, Feb-06. What do I use to convert these to date format? 1/1/06, 2/1/06. Thanks Toni

UPDATE: Changed this line: strWhere = strWhere & "(Subtype =" & Chr(39) & Me![lstSubtype].Column(0, varItem) & Chr(39) & " OR " To: strWhere = strWhere & "subtypeid =" & Me![lstSubtype].Column(0, varItem) & " OR " since the name of the field on my form "Projects" is subtypeid and is a number...

Syntax error Getting closer to what I need but am getting an error when running the sql statement. Runtime error 3075 Syntax error in query expression "SELECT * FROM qryProjects WHERE (Subtype='10') AND (status="Active") Subtype is is an integer and status is text. Can anyone see where I am...

Can't seem to locate a similar situation in the forum for this problem. I have a form based on "qryProjects". I have another form "Menu" which has cboType, lstStatus and lstSubtype. I thought that I could select criteria from each and, with a command button, have the form open up filtered...

If there's a post out there that addresses this, please point me in that direction as I've had no luck. I have my queries listed in a table. On my form I have them listed in my combo box. The user selects the query they want and hits the command button "Run". I would like to show the...

Has anyone experienced this error message before? "Could not use "V:\Project\Materials\PurchSQE\Training\MaterialsTraining_be.mdb';file already in use". Backend is on a shared network drive and everyone has their own copy of the front end. I installed the fe on several machines, but, on two...

This is exactly what I was looking for! Thanks so much, it makes sense to me as I look more into DateAdd. I'll be using that in the future for other things. Thanks again! Toni

Can this be done? I have a single $ amount and an effective date. I need to take that dollar amount and disperse it out evenly over 12 months. Is it possible to create a query that will create the fields 1/1/2006, 2/1/2006 and enter those amounts? For example, total savings is $12k and...

Think I've finally got it. Query 1: SELECT tblProjectDetails.projectid, tblVolumes.volume, tblVolumes.product, tblProjectDetails.cutindate, tblVolumes.monthdate, IIf(IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume],[reduction]*[tblVolumes].[volume]) AS Savings FROM...

I do have the cut in date field in my query. So I want to end up with: projectid cutindate calculatedsavings 001 1/1/06 5000 001 2/1/06 4500 001 3/1/06 3200 001 4/1/06 1000 002 1/1/06...

Sorry, I think I gave you the impression that I was trying to do the calculations in my crosstab query. I am trying to create one query that will give me: projectid totalsavings product The savings amount has to be calculated for every month for every project, because the volumes for each...

grnzbra, I want to try the crosstab but can't do that until I can get all of the calculated values into one field, which I am still trying to figure out. I only seem to be able to get it to create a field for each month and not all of the values into one field to enable the crosstab query. I...

SELECT tblProjectDetails.projectid, IIf(#1/1/2006#>=[cutindate] And IsNull([reduction]),([currentprice]-[newprice])*[tblVolumes].[volume],IIf(#1/1/2006#>=[cutindate],[reduction]*[tblVolumes].[volume])) AS Jan06, IIf(#2/1/2006#>=[cutindate] And...

Thanks for sticking with me on this one - it's finally making sense. I have one query field and have started my multiple IIf statements. I am getting this message: "..expression contains invalid syntax, or you need to enclose text data in quotes". The cursor moves to the comma just before my...

grnzbra, thanks for the help - still struggling with this. When you say to get to a table or query with a project field, date field and savings field, this will get me ONE calculation for the effective month of the project, right? I have tried everything I've seen and can come up with but I...

I can't believe I figured this out right after my post - after an hour of research! Sorry for the false alarm: Corrected criteria Not ("Supplies forecast" Or Like "PO*") Or Is Null Thanks, Toni

Does anyone know why this is not working for me? I am trying to change a query to add additional criteria and the records that I am trying to exclude keep showing up. Original query Field - "otherloc" Criteria - <>"Supplies forecast" Or Is Null New Criteria - <>"Supplies forecast" Or Is Null...

This will be my first attempt at this, but is a good way to do this to create a function that will loop through the dates/volumes and do the calculation in one query field rather than the need to have the calculation I was using for each and every month? Then I can use my crosstab query which I...