Forum Widgets
Latest Discussions
MS Access Handling Nulls In Query Formula
Hi all, I have this formula in Access: IIf([Init_Static_P] < 0 And (Nz([Adj_Static_P], -9999) < 0), "Resolved", "Not resolved" which keeps outputting an #Error when Adj_Static_P is null which will happen. I need the nulls in my dataset. Whenever Init_Static_P is < 0 and or Adj_Static_P is < 0, it works as it should outputting "Resolved" and vicer versa, however it does not work whenever there is any instance of Adj_Static_P as a blank value. Does anyone know how I can handle the nulls so that if the Init_Static_P is >0 and Adj_Static_P is null, then it would output "Unresolved", and if Init_Static_P is <0 and Adj_Static_P is null then it would output "Resolved" instead of #Error? Output should be as follows: Init_Static_P Adj_Static_P Result >0 Null "Not resolved" >0 >0 "Not resolved" <0 Null "Resolved" <0 < 0 "Resolved" Thanks in advance! <3
Copilot, Access, and VBA
I don't know how many of you use Copilot to assist you with Access. I developed my own application using Copilot and it was extremely helpful. I recommend you give it a try to see if it can help you. Here is an article I wrote on it in case you're interested: https://medium.com/@jmsp4home/did-my-own-spending-income-tracking-software-using-microsoft-copilot-heres-why-and-how-it-went-1aa8b4814cca?source=friends_link&sk=64ebb3ecf7e5b19c05d1731a571b1001
Sharepoint Linked Table - Help
Hi, I have an older sharepoint list that collects concrete deliveries to site (Insitu Concrete), i have a new Sharepoint list that that shows the price for each mix type (Concrete_Mix_Design_Costs). I cannot run a query or use DLookup succesfully to add the price to each delivery. Above are the tables i want to put the price against each delivery based on Mix Type. Any help would be much appreciated, I am a builder not a computer person but have muddled my way to building a database that runs our procurement etc, but despite hours of effort i am going nowhere with this. Thanks
IsMissing not working since last update
I use a few optional parameters here and there, and check to see if they have a value with the IsMissing function. As of the last update (around 11/5/24) it's no longer working. I can check to see if there is a value, and in the case of a string I can check for an empty string, but I would like not to have to recode my database. Anyone else have this issue, or is it just me? Solutions?Error message 'Microsoft Access can't open the form 'Temp.''
I work with an Access database designed 30 years ago with which I've had no problems with until today. Suddently, when I try to run some functions (crosstab query or design a query) I'm getting this error message: "Microsoft Access can’t open the form ’temp.’ It contains data that Microsoft Access doesn’t recognize. Re-create the form or, if you maintain backup copies of your database, retrieve a copy of the form.’" Sometimes the query has time to run before the message appears but once it does, the application freezes. Click OK or close the box or press Escape, the result is the same: Access closes. The form 'temp' isn't one I've created and doesn't appear on the preview pane. I can highlight it using 'analyze performance' under 'database tools' but then I get an error message 'Application-defined or object-defined error' and again the application closes. Exactly the same messages appear when I try to run archived versions of the database which I'd not opened or altered for months - so I think it's an interface issue between the design of my database and a freshly updated version of Access? I've tried the obvious (compact and repair, safe mode, re-downloading Office 365) but nothing changes this. I have tried creating a new very simple database and can run cross-tab queries in that OK.
Access to Word Document
Office 365. I have an Access report with one image (logo) and 4 subreports as a Print Preview. I need to save it as a Word .docx. Would it be best to just use the export to word command on the Print Preview tab or should I try to set it up as a merge document? It would require subdocuments, I believe. I will need to give it a unique name, based on the data in the report. Once it is in Word, can I make changes from Access? Can I re-open the Word document from Access at a later date to edit it? OR is there a better way??? Please help. Thanks in advance.
Access - how to joining two queries with different data without doubling data
I'm not sure how to phrase my issue, which makes finding a solution also difficult I'm trying to combine two different queries so I can run a report that shows all of the crops FarmerID is growing as well as the number of livestock . One query is labeled qryCrops and the other qryLivestock. Both queries have the same FarmerID (the unique key). The problem is I have a variety of scenarios: some farmers have multiple crops and only one type of livestock, other farmers have one crop and multiple types of livestock, some have multiples of both, etc. Whenever I try connecting the data, Access automatically doubles the information (see example below). I've tried joining data which hasn't worked. A query with a left join won't duplicate the crops, but will duplicate livestock. A query with a right join won't duplicate the livestock, but will duplicate the crops. I've tried running a left and right join query separately, and then combining both queries into a Union query, and my data still is getting doubled (I suspect that's not the right option as I'm not comparing apples to apples). Nothing seems to work! I've tried searching the internet and have been unsuccessful at finding a solution. Here is what I want; I want access to leave fields blank if there is no data: FarmerID Acres CropType LivestockNum LivestockType Farmer A 6 Pasture 3 Cows Farmer A 10 Hay Farmer B 15 Pasture 2 horses Farmer B 100 chickens This is what I get when I combine the data: FarmerID Acres CropType LivestockNum LivestockType Farmer A 6 Pasture 3 Cows Farmer A 10 Hay 3 Cows Farmer B 15 Pasture 2 horses Farmer B 15 Pasture 100 chickens Any thoughts? Our reports are currently set up to show the farmers and all of their crops, then the next section has the farmers and all of their livestock. I want a report that's grouped by the farmer so it shows all of their information in one place (without doubling data), then moves on to the next farmer. I'm hoping the query solution mentioned above will solve that. Thanks in advance!
