Search results

Perhaps I'm not explaining well enough. I have a split database with the FE and BE on our internal network drive. People create their own spreadsheets and create tables that are linked to my Access BE queries. What I THINK I can do, is view all of the spreadsheets that are linked to my database.

Hello, The other day I could swear I came across an article that said a feature of Access 2016 was that you could view all of the spreadsheets that are linked to your database. Was I imagining things? I can't for the life of me figure out how to do this.

Here is the SQL as it stands: SELECT tblLookup_ProgramType.ProgramType, Sum(tblProjectTransactions.Amount) AS Commitment, tblLookup_TransactionType.TransactionType FROM tblProjectTransactions RIGHT JOIN ((tblLookup_TransactionType RIGHT JOIN (tblLookup_ProgramType RIGHT JOIN (tblProject LEFT...

So I have a query that selects 3 fields: Program Type, Transaction Type, and Amount. The query picks up and sums 2 different transactions, Budget, and Commitment. The result of the query would look like this: Program Type | Amount | Transaction Type CSS | 100 | Budget CSS...

Thank you so much for helping me with this. I was always curious if I was doing this the right way, but you have put my mind at ease. Cheers

So I have created query's in Access already. The spreadsheet will only be viewed when on our network, or through a VPN. In the past I have used OLEDB connections, but only because I don't have a solid background in this sort of thing. An example connection string I was typically using is as...

Sorry, I might have confused you. The data source is the database. I will be viewing the data in Excel.

I am importing it with a wizard. The excel worksheet will be permanently linked as a table and will reside in the same location always. The back end database is always in the same place as well. No VBA code involved (unless that would be the best way)

I have an access 2010 database that is split into FE and BE. In Excel 2010, I would like to link to a query that resides in the BE of my database. What is the "best" OLE DB provider? 12.0 Access Database Engine OLE DB Provider? 15.0 Access Database Engine OLE DB Provider? Does it really...

Yes, that worked. In my past experience, I haven't been able to get a query to show up in Excel if it is using functions that Excel doesn't understand.

NZ works if you are viewing the query in Access, but not if you are linking it to Excel. Excel doesn't know what to do with NZ.

Alright, I created the TransactionDateLength field and used the LEN() function on that one, instead of using transactiondate and it worked. Thanks for pointing me in the right direction

Still doesn't work so I created a field called TransactionDateLength: Len([TransactionDate]) and it returns either a 9 or a null, but not a zero. Think that's the problem?

Here is a picture of the query as it stands.

The TransactionDate field is null in some records. So it would look something like?: Date: IIf(Len(Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1)=0," ",(Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1)) In the IIF statement, how do I tell...

I have a field that calculates a date range from a record in my query. Date: Format([TransactionDate],"yyyy") & "-" & Format([transactionDate],"yy")+1 My problem is that if TransactionDate is blank, it returns #ERROR. I am linking this query to Excel, which means I cannot use the NZ()...

I definitely won't be using lookup fields in my tables.

The reason I chose not to do this is because if we ever change something, I will have to make changes in many different forms, rather than just in a table. It is something I will keep in mind if I ever run into the maximum allowable relationships problem though. I do like the tricky method in...

Thanks for the reply plog. To answer your questions: 1. Most of those ID fields have a one-to-many relationship with a single lookup table that contain attributes for each project. The reason ContactID is in tblProject is because we would like to associate any contact person to any project...

Hello, I have a database that tracks projects and quarterly reports for those projects. Each report I create has a set number of transactions associated to it. I have attached a picture of the current relationships and just wanted to get some feedback on whether I have done it correctly. I...